function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Bruno V.Bruno V. 

Windowing Function - Help

Hi Devs! I'm starting a SuperBadge in Einstein Analytics (Einstein Analytics and Discovery Insights Specialist), currently doing the first challenge.
We need to do a chart that shows this fómula:
:User-added image

I already got Current Quarter Cancellations, but I can't do this purple square. How can I get previous quarter cancellations and subscribers?
I'm reading about windowing function, but I can't build my code.
  
q = load "Beattie_Subs";
q = filter q by 'Churn' == "Yes";
q = group q by ('Churn','Churn_Date_Year', 'Churn_Date_Quarter');
q = foreach q generate 'Churn' as 'Churn', 'Churn_Date_Year' + "~~~" + 'Churn_Date_Quarter' as 'Activity_Date(Year-Quarter)', count() as 'CurrentQuarterCancellations';
q = order q by ('Churn' asc, 'Activity_Date(Year-Quarter)' asc);

q1 = foreach q generate 'Churn_Date_Year' + "~~~" + 'Churn_Date_Quarter' as 'Activity_Date(Year-Quarter)', count() as CurrentQuarterCancellations;
sum(count(CurrentQuarterCancellations)) over ([-1 .. -1] partition by (Churn_Date_Year, Churn_Date_Quarter) order by 'Churn_Date_Year' + "~~~" + 'Churn_Date_Quarter');

q = limit q 2000;

Thanks <3
akash_dev__cakash_dev__c
Hi Bruno,

Please use the below query and it will help you to create the graph.

q = load "Beattie_Subs";

cancel = group q by ('Churn_Date_Year', 'Churn_Date_Quarter');
cancel = foreach cancel generate cancel.'Churn_Date_Year' + "~~~" + cancel.'Churn_Date_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', count() as 'New Quarter Cancellations';

cancel = group cancel by ('ActivityDate_Year~~~ActivityDate_Quarter');
cancel = foreach cancel generate cancel.'ActivityDate_Year~~~ActivityDate_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', coalesce(sum(cancel.'New Quarter Cancellations'),0) as 'New Quarter Cancellations', coalesce(sum(sum('New Quarter Cancellations')) over ([-1..-1] partition by all order by ('ActivityDate_Year~~~ActivityDate_Quarter')),0) as 'Previous Quarter Cancellations';

sub = group q by ('Subscription_Date_Year', 'Subscription_Date_Quarter');
sub = foreach sub generate sub.'Subscription_Date_Year' + "~~~" + sub.'Subscription_Date_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', count() as 'New Quarter Subcribers';

sub = group sub by ('ActivityDate_Year~~~ActivityDate_Quarter');
sub = foreach sub generate sub.'ActivityDate_Year~~~ActivityDate_Quarter' as 'ActivityDate_Year~~~ActivityDate_Quarter', coalesce(sum(sub.'New Quarter Subcribers'),0) as 'New Quarter Subcribers', coalesce(sum(sum('New Quarter Subcribers')) over ([-1..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Quarter')),0) as 'Previous Quarter Subcribers';

result = cogroup sub by 'ActivityDate_Year~~~ActivityDate_Quarter' full, cancel by 'ActivityDate_Year~~~ActivityDate_Quarter';
result = foreach result generate coalesce(sub.'ActivityDate_Year~~~ActivityDate_Quarter', cancel.'ActivityDate_Year~~~ActivityDate_Quarter') as 'ActivityDate_Year~~~ActivityDate_Quarter', sum(cancel.'New Quarter Cancellations')/(sum(sub.'New Quarter Subcribers') + sum(sub.'Previous Quarter Subcribers') - sum(cancel.'Previous Quarter Cancellations')) as 'Churn Rate';

please mark this as the best answer if it helped you.

Regards,
Akash Garg