3 Month avg value

[snehamahaveer]

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.

[Anonymous]

May be You can use Rangeavg(above(<your expression>,3))

Use this blog. Calculating rolling n-period totals, averages or other aggregations

[snehamahaveer]

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

[Anonymous]

RangeSum(Above(Sum({<Month=>} <your expression>), 0, 3))??

[snehamahaveer]

Hi Shiva,

It says Error: Error in expression: Nested aggregation not allowed.

[Anonymous]

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 ??