Meliorainsights.com
  • Home
  • Tableau CRM
  • Field Notes
  • Non-Profits
  • Our Clients
  • About
  • Home
  • Tableau CRM
  • Field Notes
  • Non-Profits
  • Our Clients
  • About
Search

Meliora Insights, LLC

​Data Visualization | CRM Analytics  (TCRM / Einstein Analytics) Consulting
​meliora - 'for the pursuit of the better'


A blog series distilling quantitative concepts /use-cases in CRM Analytics  (Einstein Analytics).

Using bindings in aggregate flex to filter an array.

3/19/2022

0 Comments

 
0 Comments

Run Rate 5

3/19/2022

0 Comments

 
Picture
Picture
0 Comments

Run rate 4

3/19/2022

0 Comments

 
Picture
0 Comments

run rate 3

3/19/2022

0 Comments

 
Picture
0 Comments

Run Rate2

3/19/2022

0 Comments

 
Picture
0 Comments

Run Rate part 1

3/19/2022

0 Comments

 
Picture
0 Comments

Run rate KPI and results bindings

3/19/2022

0 Comments

 
​Q_runrates_1
 
q = load "Fiscal_Calendar1";
q = group q by 'Fiscal_Year';
q = foreach q generate 'Fiscal_Year' as 'Fiscal_Year', count() as 'count';
q = order q by 'Fiscal_Year' asc;
q = limit q 2000;
 

 
Q_kpi_CurrentFiscal_1  < this query takes todays date and buckets it in correct FY bins
q = load \"Fiscal_Calendar1\";\nq = group q by ('Date', 'Fiscal_Year', 'Fiscal_Quarter', 
'Fiscal_Month_Number_Month', 'Fiscal_Month', 'Fiscal_Week');\n
 
q = foreach q generate (date_to_string(now(),\"MM/dd/yyyy\")) as 'Today', 
'Date' as 'Date', 'Fiscal_Year' as 'Fiscal_Year', 'Fiscal_Quarter' as 'Fiscal_Quarter',
 'Fiscal_Month_Number_Month' as 'Fiscal_Month_Number_Month', 'Fiscal_Month' as 'Fiscal_Month',
 'Fiscal_Week' as 'Fiscal_Week';\n
 
q = foreach q generate 'Today', 'Date' as 'Date', 'Fiscal_Year', 'Fiscal_Quarter', 
'Fiscal_Month_Number_Month', 'Fiscal_Month', 'Fiscal_Week';
 

 
\nq = filter q by 'Date' == 'Today';\
nq = foreach q generate 'Date', 'Fiscal_Year', 'Fiscal_Quarter', 
'Fiscal_Month_Number_Month', 'Fiscal_Month', 'Fiscal_Week',(case when 'Fiscal_Month' == \"October\" 
then \"September\" when 'Fiscal_Month' == \"November\" then \"October\" when 
'Fiscal_Month' == \"December\" then \"November\" when 'Fiscal_Month' == \"January\" 
then \"December\" end) as 'PrevMonth',(case when 'Fiscal_Quarter' == \"Q1\" then \"Q4\" 
when 'Fiscal_Quarter' == \"Q2\" then \"Q1\" when 'Fiscal_Quarter' == \"Q3\" then \"Q2\" when
 'Fiscal_Quarter' == \"Q4\" then \"Q3\"end) as 'PrevQtr',(case when ('Fiscal_Quarter' == 
\"Q1\" || 'Fiscal_Month' == \"July\")  then \"2021\" else \"2022\" end) as 'PrevFY';\n
 

 
q = foreach q generate 'Date', (case when \"{{cell(q_dynamicDate_1.selection,0,\"Valu\").asObject()}}\"
 == \"PrevMTD\" then 'PrevMonth' else  'Fiscal_Month' end) as 'Fiscal_Month',
(case when \"{{cell(q_dynamicDate_1.selection,0,\"Valu\").asObject()}}\" == 
\"PrevQTD\" then 'PrevQtr' else  'Fiscal_Quarter' end) as 'Fiscal_Quarter',
(case when (\"{{cell(q_dynamicDate_1.selection,0,\"Valu\").asObject()}}\" == \"PrevQTD\"   ||
 \"{{cell(q_dynamicDate_1.selection,0,\"Valu\").asObject()}}\" == \"PrevMTD\") then 'PrevFY' else
 'Fiscal_Year' end) as 'Fiscal_Year';",
 

 
 
Above binding is a pillbox/staticflex
 
 
 query so user either chooses the 2 prev qtr or month which then calculates proper monthqtr and year matching. else defaults to current.
Labels and chart titles also should have bindings


 
 
 
 Qtr or month12/2/2021 / December / q2 /  fy22
 
Q_kpi_DateBinding is a query that uses the dynamic date choice pillbox to determine which filter to turn  ‘off’ by inserting a “—” in the main query. Here is the query
 "q_kpi_dateBinding_1": {
                "broadcastFacet": true,
                "groups": [],
                "label": "q_kpi_dateBinding",
                "numbers": [],
                "query": "q = load \"Fiscal_Calendar1\";\nq = group q by all;\nq = foreach q generate  \"{{cell(q_dynamicDate_1.selection,0,\"Valu\").asObject()}}\" as 'Date Selected';\nq = foreach q generate 'Date Selected', (case when 'Date Selected' in [\"MTD\",\"PrevMTD\"] then \"\" else \"--\" end) as 'MTD_Binding', (case when 'Date Selected' in [\"QTD\",\"PrevQTD\"] then \"\" else \"--\" end) as 'QTD_Binding';",
 
And the query result when MTD is picked

When QTD is picked, query resolves to  QTD /  -- / null for Date Selected , MTD_Binding , QTD binding respectively
This q_kpi_date binding is then bound to main query ‘q_runrates. As you can see form the SAQL, the kpiDateBinding SITES BEFORE the filters. This embed either a “” or a “—”  in the q=filter q by…..
Embedding a”” keeps that filter ‘on”, embedding a “—” turn off that filter.
 
                "query": "q = load \"Fiscal_Calendar1\";\nq = filter q by 'Fiscal_Year' == 
\"FY22\";\n{{cell(q_kpi_dateBinding_1.result,0,\"QTD_Binding\").asObject()}}
q = filter q by 'Fiscal_Quarter' == \"{{cell(q_kpi_CurrentFiscal_1.result,0,\"Fiscal_Quarter\")
.asObject()}}\";\n{{cell(q_kpi_dateBinding_1.result,0,\"MTD_Binding\").asObject()}}
q = filter q  by 'Fiscal_Month'== \"{{cell(q_kpi_CurrentFiscal_1.result,0,\"Fiscal_Month\").
asObject()}}\";\nq = filter q by date('Date_Year', 'Date_Month', 'Date_Day') 
in [\"5 years ago\"..\"current day\"];\nq = group q by 'Fiscal_Year';\n
q=foreach q generate 'Fiscal_Year' as 'FY', sum('Shipping_Day_Num')- 1 as 'Days Passed'
;\ntotDays = load \"Fiscal_Calendar1\";\ntotDays= filter totDays by 'Fiscal_Year'== \"FY
22\";\n{{cell(q_kpi_dateBinding_1.result,0,\"QTD_Binding\").asObject()}}totDays = filter to
tDays  by 'Fiscal_Quarter' == \"{{cell(q_kpi_CurrentFiscal_1.result,0,\"Fiscal_Quarter\").as
Object()}}\";\n{{cell(q_kpi_dateBinding_1.result,0,\"MTD_Binding\").asObject()}}totDays = filt
er totDays by 'Fiscal_Month'== \"{{cell(q_kpi_CurrentFiscal_1.result,0,\"Fiscal_Month\").asObj
ect()}}\";\ntotDays = group totDays by 'Fiscal_Year';\ntotDays = foreach 'totDays' generate
 'Fiscal_Year' as 'FY', sum('Shipping_Day_Num') as 'Total Days';\nfo = load \"zds_AI_AcctHomeMgrv
4\";\nfo = filter fo by 'Fiscal_Year__c' == \"2022\";\n{{cell(q_kpi_dateBinding_1.result,0,\"QTD
_Binding\").asObject()}}fo = filter fo by 'FiscalCdr2All.Fiscal_Quarter' == 
\"{{cell(q_kpi_CurrentFiscal_1.result,0,\"Fiscal_Quarter\").asObject()}}\";\n
{{cell(q_kpi_dateBinding_1.result,0,\"MTD_Binding\").asObject()}}
fo = filter fo by 'FiscalCdr2All.Fiscal_Month'== \"{{cell(q_kpi_CurrentFiscal_1.result,0,
\"Fiscal_Month\").asObject()}}\";\nfo = group fo by all;\n
fo = foreach fo generate sum('{{column(q_RevenueGP_1.selection,[\"Valu3\"]).asObject()}}') as 
'Forecast', sum('{{column(q_RevenueGP_1.selection,[\"Valu1\"]).asObject()}}') as 'Quota',
 sum('{{column(q_RevenueGP_1.selection,[\"Valu2\"]).asObject()}}') as 'Actuals';\n
results=union q,totDays,fo;\nresults = group results by all;\nresults = 
foreach results generate coalesce(sum('Quota'),0) as 'Quota',coalesce(sum('Forecast'),0) as
 'Forecast', coalesce(sum('Actuals'),0) as 'Actuals', sum('Total Days') as 'Total Days', 
sum('Days Passed') as 'Days Passed';\nresults = foreach results generate 'Actuals', 'Forecast',
 'Quota', 'Days Passed' as 'Days Passed', 'Total Days' as 'Total Days','Actuals' / 'Days Passed' as 'A'
,'Quota' / 'Total Days' as 'B';\nresults = foreach results generate 'Actuals', 'Forecast', 'Quota', 
'Days Passed' as 'Days Passed', 'Total Days' as 'Total Days','A' as 'A','B' as'B','A' /
  'B' as 'Run Rate','Quota' - 'Actuals' as 'To Quota';\nresults = foreach results generate 'Actuals',
 'Forecast', 'Quota', 'Days Passed' as 'Days Passed', 'Total Days' as 'Total Days','Run Rate',('Quota'
 * 'Run Rate') as 'RR Dollars';"
 
 
 
 
0 Comments

Use of SAQL Index-Of() to pick out certain roles in a Flatten Transformation - by Dan Alindogan

3/8/2022

0 Comments

 

Here’s an interesting use-case for a client of ours. They need reports that will be available to users who have a certain role ‘X” as well as having the same security for the role 2 levels up. This means that it has to bypass the next level up. Illustratively, given a role path of x / y / z / k / m, the data needs to be seen by roles x and z. In order to effect that, a flatten transformation with the self-Id checked is used, followed by a compute expression and the’ index_of’ string function. Listed below are the steps and settings as well as a link to the SAQL function  “index_of” . 

To start off, the function “index_of” resolves to an integer marking the location of a specific character inside a string. 


https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_functions_string_io.htm

index_of(“connecticut” , “c”, 1,1) = 1
index_of (“connecticut”, “c”, 1, 3) = 9
index_of(“connecticut”, “c” 1,4) = 0

Why should someone integrate the function “index_of” into their SAQL? One reason is to  isolate certain strings and use them when there is a flatten node dealing with Role Path. 
Here is an example of how we can use the “index_of” in a case where the role is in the security predicate. We want to show how we can filter out the appropriate role out of the string created by the flatten transformation.

Role Path:
 
  1. defg/hijk/lmno
  2. abcd/efgh/ijkl
  3. Wxyz
  4. Wwww/eeee/ffff/tttt/rrrr
Compute expression: How can we leave out any roles other than the 1st and 3rd? (with Self ID turned on in the flatten transformation node)

substring(‘Product’, 
            , case index_of (“/”, 1, 2) = 10 then 10
            , case index_of(“/”, 1,1) = 5 then 0 
                    Else 0 ) then +1, 4)

Resolves to:
  1. substring(‘Product’, 11, 4) = lmno
  2. substring(‘Product’, 11, 4) = ijkl
  3. substring (‘Product’, 1,4) = wxyz
  4. substring (‘Product’, 1,4) = Wwww

Note: the 2nd case statement (, case index_of(“/”, 1,1) = 5 then 0 )  is superfluous but is there to show that anything other than the 2nd occurrence is “/” is ignored since we are interested in the self id and the 3rd role.

After assigning the result of the ‘Resolves to’ list to a variable called ’ProperRole’, one can then insert it in the security predicate (ie… ‘ProperRole’ ==  $user.RoleID )

​
0 Comments

Compute relative vs. Compute expression. What is the difference?---- Dan Alindogan

1/31/2022

0 Comments

 
An interesting use-case came up with one of our supply chain clients. They needed to bin certain invoices into 1 of two categories--1) Full Service (any invoice that contains a 'Service Contract' line item OR 2) Retail (any invoice that does not have a service contract). This use-case utilizes 2 powerful TCRM nodes-- the compute relative and the compute expression.


At a high level, the compute relative enables a comparison up and down the table (ie rows) while the compute expression goes left > right ie columns. Down below I will show a step by step guide to how one would tackle this use case.


Step 1: Create a compute expression and label it as ‘Recasted Label’ (SAQL pseudocode follows)
Load ‘[Dataset]’


Case when ‘Description’’ == “Labor” then “AAA” else “[Description]”



Step 2: Use a Compute Relative Node to create a field called ‘Bin Type’ using the following settings in the Dialogue Box of that node
Group by InventorySort by ‘Recasted Label’ Asc
Create new field —> ‘Bin Type’
Aggregate f(x) = ‘first’



Step 3: Use a Compute Expression to create a new field “Class” using a CASE Statement.
 Case when ‘Bin Type’ != “AAA”    Then “Ship” else “Comprehensive”





Picture
Picture
0 Comments

Creating a waterfall chart from a time-series dataset

11/21/2021

0 Comments

 
so let's say you have a set of metrics that you want to create a time-series on. Maybe your team makes sales forecast on clients that changes over time. You can take a 'snapshot' of that forcast daily thru a scheduled dataflow and an append transformation node. At the end of a certain period your dataset may look like this...
Client Date Forecast 
A   January 3  $1000
B  January 3   $5000
A  March 2     $2000
B March 2     $3000
Code below creates a waterfall chart with a binding to control the grouping trigger.
q = load \"zds_IoTForecast5\";\nstartVal = filter q by {{column(qDateTest_1.selection,[\"cTimeStampText\"]).asEquality('cTimeStampText')}};\nendVal = filter q by {{column(q_SnapDate2_1.selection,[\"cTimeStampText\"]).asEquality('cTimeStampText')}};\nq_A = filter q by {{column(qDateTest_1.selection,[\"cTimeStampText\"]).asEquality('cTimeStampText')}};\nq_B = filter q by {{column(q_SnapDate2_1.selection,[\"cTimeStampText\"]).asEquality('cTimeStampText')}};\nstartVal = group startVal by all;\nstartVal = foreach startVal generate \"Start Value\" as '{{column(static_6.selection,[\"Valu\"]).asObject()}}',number_to_string(sum (startVal.'TotValueUSD'),\"$#,###\") as 'Forecast Delta';\nendVal = group endVal by all;\nresult = group q_A by '{{column(static_6.selection,[\"Valu\"]).asObject()}}' full, q_B by '{{column(static_6.selection,[\"Valu\"]).asObject()}}';\nresult = foreach result generate coalesce(q_A.'{{column(static_6.selection,[\"Valu\"]).asObject()}}', q_B.'{{column(static_6.selection,[\"Valu\"]).asObject()}}') as '{{column(static_6.selection,[\"Valu\"]).asObject()}}',coalesce(sum(q_A.'TotValueUSD'),0) as 'A', coalesce(sum(q_B.'TotValueUSD'),0) as 'B';\nresult = foreach result generate '{{column(static_6.selection,[\"Valu\"]).asObject()}}',number_to_string( B-A,\"$#,###\") as 'Forecast Delta';\nendVal = foreach endVal generate \"Ending Value\" as '{{column(static_6.selection,[\"Valu\"]).asObject()}}',number_to_string(sum (endVal.'TotValueUSD'),\"$#,###\") as 'Forecast Delta';\nresult = order result by '{{column(static_6.selection,[\"Valu\"]).asObject()}}' asc;\nfinal = union startVal,result,endVal;
 
 


Picture
0 Comments
<<Previous
Forward>>
Site powered by Weebly. Managed by Hostwinds
  • Home
  • Tableau CRM
  • Field Notes
  • Non-Profits
  • Our Clients
  • About