topic Re: Calculating average upto the month in QlikView

Calculating average upto the month

Fri, 16 Mar 2012 01:23:37 GMT

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.

Re: Calculating average upto the month

Fri, 16 Mar 2012 04:14:57 GMT

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

Re: Calculating average upto the month

Mon, 19 Mar 2012 04:31:36 GMT

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

Re: Calculating average upto the month

jvitantonio — Mon, 19 Mar 2012 09:21:05 GMT

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

I hope it's helpful.

Re: Calculating average upto the month

Thu, 22 Mar 2012 11:02:44 GMT

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