Skip to content

Custom Fiscal Week (Monday to Sunday) in Wave Analytics (Salesforce Einstein Analytics)
q = load “GR_Opportunities”;
q = foreach q generate ‘CloseDate_day_epoch’ as ‘CloseDate_day_epoch’, ‘CloseDate_sec_epoch’ as ‘CloseDate_sec_epoch’, ‘Id’ as ‘Id’, ‘StageName’ as ‘StageName’,  daysBetween(toDate(“24-06-1985”, “dd-MM-yyyy”), toDate(CloseDate_sec_epoch)) % 7 as ‘datediff’;
q= foreach q generate (case
when datediff == 6 then (case when daysBetween(now(), toDate(CloseDate_sec_epoch))>=-6 and daysBetween(now(), toDate(CloseDate_sec_epoch))<=0 then “CurrentWeek” else “OtherWeek” end)
when datediff == 5 then (case when daysBetween(now(), toDate(CloseDate_sec_epoch))>=-5 and daysBetween(now(), toDate(CloseDate_sec_epoch))<=1 then “CurrentWeek” else “OtherWeek” end)
when datediff == 4 then (case when daysBetween(now(), toDate(CloseDate_sec_epoch))>=-4 and daysBetween(now(), toDate(CloseDate_sec_epoch))<=2 then “CurrentWeek” else “OtherWeek” end)
when datediff == 3 then (case when daysBetween(now(), toDate(CloseDate_sec_epoch))>=-3 and daysBetween(now(), toDate(CloseDate_sec_epoch))<=3 then “CurrentWeek” else “OtherWeek” end)
when datediff == 2 then (case when daysBetween(now(), toDate(CloseDate_sec_epoch))>=-2 and daysBetween(now(), toDate(CloseDate_sec_epoch))<=4 then “CurrentWeek” else “OtherWeek” end)
when datediff == 1 then (case when daysBetween(now(), toDate(CloseDate_sec_epoch))>=-1 and daysBetween(now(), toDate(CloseDate_sec_epoch))<=5 then “CurrentWeek” else “OtherWeek” end)
when datediff == 0 then (case when daysBetween(now(), toDate(CloseDate_sec_epoch))>=0 and daysBetween(now(), toDate(CloseDate_sec_epoch))<=6 then “CurrentWeek” else “OtherWeek” end)
else “Blank Close Date”
end) as currentweek, Id as ‘Id’;
q = filter q by currentweek == “CurrentWeek”;
q = limit q 100;