Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a formula that calculate the no of customers that exceed specified limits and hence classified as engaged.
Now I need to amend this formula, to calculate the avg of last 3 month values (values calculated from the formula below)
=if(sum({<Transaction_Category = {'Oil'}>}Sales_Value)>100,
count( aggr( if(
(sum({<Transaction_Category = {'Oil'}>}Sales_Value) >=
(sum({<Transaction_Category = {'Oil','Fruits','Vegetables'}>}Sales_Value)*0.10)),
(sum({<Transaction_Category = {'Oil'}>}Sales_Value)/
sum({<Transaction_Category = {'Oil','Fruits','Vegetables'}>}Sales_Value))),
Customer, [Month Year])))
eg Month, Engaged Customers
Apr, 45
May, 40
Jun, 44
Jul, 50
Then value for Jun => (45+40+44)/3 = 43, Jul => (40+44+50)/3 = 45
Any help is much appreciated, can someone please help me with this urgent request. Thank you.
May be You can use Rangeavg(above(<your expression>,3))
Use this blog. Calculating rolling n-period totals, averages or other aggregations
Hi Shiva,
I did try this, however the first two months in the selection is affected and the values are correct only from 3rd Month.
=sum(aggr(Rangesum(above(total if(sum({<Transaction_Category = {'Oil'}>}Sales_Value)>100,
count( aggr( if(
(sum({<Transaction_Category = {'Oil'}>}Sales_Value) >=
(sum({<Transaction_Category = {'Oil','Fruits','Vegetables'}>}Sales_Value)*0.10)),
(sum({<Transaction_Category = {'Oil'}>}Sales_Value)/
sum({<Transaction_Category = {'Oil','Fruits','Vegetables'}>}Sales_Value))),
Customer, [Month Year]))) ,0,3)), [Month Year]))/3
RangeSum(Above(Sum({<Month=>} <your expression>), 0, 3))??
Hi Shiva,
It says Error: Error in expression: Nested aggregation not allowed.
Use Month= in the set expressions of your expression. The error is because we are doing sum on aggregated value. Can you share a sample ??