how to do sum of values which is subtract with avg of last three month of last three months?

[jeevays7]

Date       month      value  ineedthis

1-mar     mar     7    

1-mar     mar     8

1-apr       apr       5     5 - 5.5

2-apr       apr        6     6 - 5.5

1-may      may     5     5 - 5.5

2-may       may     5      5 - 5.5

1-jun       jun       6      6 - 5.5

2-jun       jun        6     6 - 5.5

avg of last three months 33/6=5.5,

so,

i need sum of 5.5 - (value) for last three months. (ineedthis) column using set analysis or else.

for every month we do last three month of avg should subtracted to all values of last three month and sum of that value.

How do to?

[Anonymous]

Hi

btw

avg of last three months 33/6=5.5,

Are you sure of this  or  33 / 3 ?

[maxgro · Accepted solution]

In a script I think you can use the as-of to calculate the average as explained here

Calculating rolling n-period totals, averages o... | Qlik Community

[jeevays7]

Hi Alluraiah,

Yes im sure. only 6. (no.of days in 3 months)

[jeevays7]

Hi Maxgro,

I can add three months to each of the months and calculate avg using AsOf_Table.

But how can i calculate , sum(ineedthis)?

Ex:

Date       month      value  ineedthis

1-mar     mar     7   

1-mar     mar     8

1-apr       apr       5     5 - 5.5

2-apr       apr        6     6 - 5.5

1-may      may     5     5 - 5.5

2-may       may     5      5 - 5.5

1-jun       jun       6      6 - 5.5

2-jun       jun        6     6 - 5.5