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;

#3 Filtering 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’;