topic Re: 3 Month avg value in QlikView

3 Month avg value

snehamahaveer — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: 3 Month avg value

Anonymous — Fri, 28 Apr 2017 10:01:12 GMT

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

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

Re: 3 Month avg value

snehamahaveer — Fri, 28 Apr 2017 10:19:06 GMT

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

Re: 3 Month avg value

Anonymous — Fri, 28 Apr 2017 10:34:52 GMT

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

Re: 3 Month avg value

snehamahaveer — Fri, 28 Apr 2017 10:44:55 GMT

Hi Shiva,

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

Re: 3 Month avg value

Anonymous — Fri, 28 Apr 2017 11:49:58 GMT

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