|
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';" Use of SAQL Index-Of() to pick out certain roles in a Flatten Transformation - by Dan Alindogan3/8/2022 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:
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:
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 ) 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”   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;  |
