1

This should be some basic stuff but lack of good documentation on SAQL is making it hard to figure out. I just want to do a simple math like ths-

Avg. Sale Price = Total Sale Price/Qty

The filters used for computing Total Sale Price and Qty are different. For example,

a = load ...
b = filter a by ... //some filters here
b = group b by all;
b = foreach b generate sum('Sale_Price') as 'total_sale_price';

c = filter a by ... //some filters here (different from those used for b)
c = group c by all;
c = foreach c generate count() as 'qty';

Now, I just want to compute b.total_sale_price/c.qty. How can I do that?

2
  • Just reading some of the documentation ? developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_eql.meta/… -- Seeing how b = Total_sales_price and c = qty... could you not do d = b/c? Or try d = Total_Sales_price/qty?
    – AlphaBravo
    Jun 17, 2016 at 12:07
  • b and c are streams (like tables), so what you're saying is b.total_sale_price/c.qty That doesn't work because the fields are not part of same stream. One is from b and another from c Jun 17, 2016 at 19:24

3 Answers 3

1

You can also do a cogroup on two different data streams. This is also useful for Year over Year calculations. The one challenge with a cogroup is that the fields that you are cogrouping must be the same (Similar to an inner join).

a = load .. 
b = filter a by ..
b = group b by 'Join_Field_1';
b = foreach b generate 'Join_Field_1' as 'Join_Field_1', sum('Sale_Price') as 'total_sale_price';

c = load ..
d = filter c by (different filters)
d = group d by 'Join_Field_2';
d = foreach d generate 'Join_Field_2' as 'Join_Field_2', count() as 'qty';

cg = cogroup b by 'Join_Field_1', d by 'Join_Field_2';
res = foreach cg generate sum(b.'total_sale_price')/sum(d.'qty') as 'sum_avg_sale_price'
0

Looks like it can be done using pseudo fields. There's probably a better way but this works..

 
b = foreach b generate sum('Sale_Price') as 'total_sale_price', 0 as 'qty'; 

c = foreach c generate 0 as 'total_sale_price', count() as 'qty'; 

d = union b, c; //pseudo fields allow you to do a union
d = group d by all; 
d = foreach d generate sum('total_sale_price')/sum('qty') as 'avg_sale_price';
0

You can use Windowing Functions

From the Percentage Total example in the documentation

q = load "dataset";
q = group q by (OrderDate_Year, OrderDate_Quarter);
q = foreach q generate OrderDate_Year as Year, OrderDate_Quarter as Quarter, sum(Sales) as sumSales, (sum(Sales) * 100) / sum(sum(Sales)) over([..] partition by OrderDate_Year) as p_tot;
  • sum(Sales) is the total Sales amount per year per Quarter
  • sum(sum('Sales') is the total Sales amount for Quarters in the current Year
  • sum(Sales) * 100 / sum(sum('Sales') calculates the percentage that Quarter contributed to the total Sales amount in the current Year
  • [..] means "include all records in the partition"

Percentage of Total Result

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .