Calculating average upto the month

[#1]

Hi All,

     Please find the attached excel book below. It contains two table, left one is the detailed data by year, month, day. and the right one is the required output.

     I need an idea for how to write a script or an expression in the chart that will calculate the average by month.

     for example:

Year	Month	Sum	Average
2012	1	14923	14923.00
2012	2	15639	15281.00
2012	3	16094	15552.00

In the above table the average value is calculated like For month Jan, avg is 14923/1=14923, For feb avg is 14923+15639/2=15281 and so on. We can call it as accumulated avg by month.

In qlikview, I struggling to calculate this avg value.

Please help me in this.

Thanks in advance.

[#2]

Try this....showing correct Ave for 2012 but for year change it again restarts....

found the attached QVW file

hope it help you

Regards

Arun Goel

[#3]

if u get better solution then this...plz share..

[jvitantonio]

Hi, Here's the complete solution for your problem.

I hope it's helpful.

[#5 · Accepted solution]

Guys,

     Thanks for trying.

     I've found the solution.

     In expression, above(Value, 0, 12) can be used to calculate the average of last 12 months.

     After that it can be aggregated by year and month using aggr method.

     Thanks to all of you.

Regards

Rajan