topic Help with Running Average in QlikView

Help with Running Average

Thu, 13 Aug 2009 20:57:53 GMT

Hi Everyone,

I am new to QlikView and have an issue I would like help with. I have a simple production report setup as a straight table. The dimension is the Date, the first expression is the sum of volume produced that day and the second is the same expression but with full accumulation checked so that total volume will be accumulated. The third expression needs to be a running average of the total volume / # of days so far. so for example, it should look something like this:

Date	Daily Production	MTD	Avg
1-Jan	5	5	5
2-Jan	6	11	5.5
3-Jan	10	21	7

as you can see, for 1-Jan the AVG is 5 divided by 1. For 2-Jan it's 11 divided by 2 and for 3-Jan it's 21 divided by 3.

I am using the day() function to get the number of days to divide by, but I cannot figure out how to capture the total MTD volume on each row to create this average. Any suggestions?

Thanks!

Elizabeth

Help with Running Average

Thu, 13 Aug 2009 21:06:24 GMT

Hi,

Please use Peek and Previous function in the script.

Regards

Amit

Help with Running Average

Thu, 13 Aug 2009 21:12:39 GMT

Is there anything that can be used within the chart expression?

Help with Running Average

Thu, 13 Aug 2009 21:46:45 GMT

Hi EPayne,

any relation to TPayne?? har har...

I would do this to get MTD Volume:


sum (total <FiscYear, Month> Volume)

Your formula may look a bit different depending on your table names for fiscal year, month, and volume. Let me know if you need more clarification.

Help with Running Average

johnw — Fri, 14 Aug 2009 03:01:13 GMT

Attached is one approach.