0

I'm stuck with a problem and I want to make a sum only under certain criteria, for example:

Add the Total field per month and then take the average of the last 3 months ie

sum (total) -> if month = 1 as Total_1 sum (total) -> if month = 2 as Total_2 sum (total) -> if month = 3 as Total_3

avg = total_1 + total_2 + total_3 / 3

That would be the average of the last 3 months

I can not think of how to perform in a query with saql a sum only based on certain criteria

result = foreach result generate sum (data.'total ') if month = 1 as 
Total_1, sum (data.'total') if month = 2 as Total_2, sum (data.'total 
') if month = 3 as Total_3, ((total_1 + total_2 + total_3) / 3) as 
AVG;
  • This is Wave Analytics (SAQL) not APEX (SOQL) – ojedawinder May 24 '18 at 9:51
1

You should be able to do this using multiple filter and grouping statements.

So using your example, try filtering the dataset by months 1, 2, and 3 first. From there, you can then get the monthly totals by grouping the dataset by month. Last step would be to put them all in one group to calculate the average.

Something like this:

data = filter data by month in ["1", "2", "3"]; -- we're only interested in data for months 1, 2 and 3
data = group data by month;
data = foreach data generate month, sum('total') as 'month total'; -- the output of this statement is 3 rows, each with a monthly total
data = group data by all;
result = foreach data generate avg('month total') as '3 months average';
0

Finally I create some data sets to get the desired result in just one row:

Basically it was

q_A = load "datos";


q_A1 = filter q_A by date('Fecha__c_Year', 'Fecha__c_Month', 'Fecha__c_Day') in ["current month - 3 month".."current month - 3 month"];
result1 = group q_A1 by ('Field1');
result1 = foreach result1 generate q_A1.'Field1' as 'Field1', coalesce(sum(q_A1.'Amount'),0) as 'Amount1';
result1 = order result1 by 'Field1' asc;
result1 = limit result1 2000;

q_A2 = filter q_A by date('Fecha__c_Year', 'Fecha__c_Month', 'Fecha__c_Day') in ["current month - 2 month".."current month - 2 month"];
result2 = group q_A2 by ('Field1');
result2 = foreach result2 generate q_A2.'Field1' as 'Field1', coalesce(sum(q_A2.'Amount'),0) as 'Amount2';
result2 = order result2 by 'Field1' asc;
result2 = limit result2 2000;

q_A3 = filter q_A by date('Fecha__c_Year', 'Fecha__c_Month', 'Fecha__c_Day') in ["current month - 1 month".."current month - 1 month"];
result3 = group q_A3 by ('Field1');
result3 = foreach result1 generate q_A3.'Field1' as 'Field1', coalesce(sum(q_A3.'Amount'),0) as 'Amount3';
result3 = order result3 by 'Field1' asc;
result3 = limit result3 2000;

Then I make a union

result = union result1, result2, result3;

result = group result by ('Field1');
result = foreach result1 generate result.'Field1' as 'Field1', 
coalesce(sum(result.'Amount1'),0) as 'Amount1',
coalesce(sum(result.'Amount2'),0) as 'Amount2', 
coalesce(sum(result.'Amount3'),0) as 'Amount3',
coalesce(sum(result.'Amount1')+ sum(result.'Amount2')+ 
sum(result.'Amount3'),0) / 3 as 'AVG';
result = order result3 by 'Field1' asc;
result = limit result3 2000;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.