Einstein Analytics – SAQL –
#1 Year to Date Comparison from Current Year vs Previous Year SAQL : Einstein Analytics
if you are Einstein Analytics Developer you would have come across a Data visualization requirement to compare the revenues from current Year to Date Vs Previous Year to Date. This is one of the important KPI for Leader dashboard .This can be done by customizing the SAQL .
Last Year to Date (if invoice date is calendar year)
r = load “Invoice_Data”;
r = filter r by date(‘INVOICEDATE_Year’, ‘INVOICEDATE_Month’, ‘INVOICEDATE_Day’) in [“1 year ago” .. “current day – 1 year”];
r = group r by (‘REGION’);
r = foreach r generate ‘REGION’ as ‘Region’, sum(‘revenue_amount’) as ‘Last Year to Date’;
r = limit r 2000;
Current Year to Date (if invoice date is fiscal year)
r = load “Invoice_Data”;
r = filter r by date(‘INVOICEDATE_Year’, ‘INVOICEDATE_Month’, ‘INVOICEDATE_Day’) in [“current fiscal_year” .. “current day “];
r = group r by (‘REGION’);
r = foreach r generate ‘REGION’ as ‘Region’, sum(‘revenue_amount’) as ‘Current Year to Date’;
r = limit r 2000;
Last Year Month to Date
q = load “Invoice_Data”;
q = filter q by date(‘INVOICEDATE_Year’, ‘INVOICEDATE_Month’, ‘INVOICEDATE_Day’) in [“current month – 1 year”..”current day -1 year”];
q = foreach q generate ‘INVOICEDATE’ as ‘INVOICEDATE’;
q = order q by ‘INVOICEDATE’ desc;
q = limit q 2000;
#2 Filtering in SAQL using equal to clause
q = load “Invoice_Data”;
q= filter q by ‘SF_Acco.RecordTypeId.Name’ == “Partner Account”;
q= group q by ‘SF_Acco.Name’;
q= foreach q generate count() as ‘Total Record’,’SF_Acco.Name’ as ‘Name’;
limit q 100;
#3Filtering in SAQL using In clause
q = load “Invoice_Data”;
q= filter q by ‘SF_Acco.RecordTypeId.Name’ == “Partner Account” and ‘FISCALYEAR’ in [“2017″,”2016”];
q= group q by (‘SF_Acco.Name’,’SF_Acco.RecordTypeId.Name’,’FISCALYEAR’);
q= foreach q generate count() as ‘Total Record’,’SF_Acco.Name’ as ‘Name’,’SF_Acco.RecordTypeId.Name’ as ‘RT Name’,’FISCALYEAR’ as ‘Current Year’,sum(‘revenue_amount’) as ‘Revenue’;
limit q 100;
#4 Co Grouping
a = load “Invoice_Data”;
a = filter a by ‘TERRITORYNAME’ matches “US”;
a = filter a by date(‘INVOICEDATE_Year’, ‘INVOICEDATE_Month’, ‘INVOICEDATE_Day’) in [“current year”..”current year”];
b = load “Invoice_Data”;
b = filter b by ‘TERRITORYNAME’ matches “US”;
b = filter b by date(‘INVOICEDATE_Year’, ‘INVOICEDATE_Month’, ‘INVOICEDATE_Day’) in [“1 year ago” .. “current day – 1 year”];
b = cogroup b by ‘REGION’, a by ‘REGION’;
c = foreach b generate b.REGION as ‘Region’, sum(a.revenue_amount) as ‘2017’, sum(b.revenue_amount) as ‘2016’;