9 Replies Latest reply: Jul 25, 2011 10:44 AM by chrispfeiffer

# Cumulative Average Calculations

Hi - as you may guess from this, I am very new to QlikView but need to get something out very fast! I have some raw data which contains details of customers and their spend by date. Each date also sits within a month and month is the dimension I'm using in this instance. I have created three expressions which show me, by month, the count of customers, the total spend and the average spend.

I now need to show the cumulative average spend but am having difficulties! The accumulation options don't pull back the correct figures. Will this require a complex new expression or is there an easier was to resolve this?

Thanks

• ###### Cumulative Average Calculations

Hello,

Thanks,

Amand

• ###### Re: Cumulative Average Calculations

Hi Amand – just preparing the file. Have had to delete some data because it is private from the client’s persepctive

Chris Pfeiffer || Mob: +44 (0) 7949 215160 || E-Mail:  <mailto:chris@reporting4retail.com> chris@reporting4retail.com || Web:  <http://www.reporting4retail.com/> www.reporting4retail.com

• ###### Cumulative Average Calculations

Hi - file uploaded. There are four charts showing. The top three are correct. It's the bottom one which is incorrect - it should show something close to (but not exactly the same as) chart number 2

• ###### Re: Cumulative Average Calculations

Hi,

You have two ways to obtain the accumulated value.

One is via the "Accumulation" Expression tab.

The second is through the functions Above () or Before ().

see attached example

• ###### Cumulative Average Calculations

Hi - I'm unable to open the attachment as I'm on the personal version. I do know, however, that the accumulation option doesn't work for the averages - all it does is cumulate each monthly average instead of giving a new cumulative average at the end of each month. As I mentioned earlier, I am very new to this and haven't got to using the above and below functions yet but will check the 'help' function on these to check

• ###### Cumulative Average Calculations

Hi,

See this steps to create the document.

Script:

TMP:

Month, Value

01, 100

02, 150

03, 125

04, 95

05, 100

06, 130

];

Create a Straight Chart with Month as Dimension

RANGESUM(AVG(Value),ABOVE(AVG(Value),1,ROWNO()))

Regards

• ###### Re: Cumulative Average Calculations

Hi Eduardo,

I am not sure that this is correct, where do the customer count come into play for the average?

Chris,

try this for expression in chart 4:

=RangeSum(above(sum(CustomerSpend),0,rowno()))/RangeSum(above(sum([Customer Count]),0,rowno()))

Regards,

Stefan

Added application for all who can open

Nachricht geändert durch swuehl

• ###### Re: Cumulative Average Calculations

Thanks Eduardo but it’s giving me exactly the same as a full accumulation. The second chart below is the cumulative result of the monthly data shown in the first chart/ The third chart is the average spend by month. I’d expect the fourth chart to be pretty close to the third chart but, as you can see, it’s cumulating each individual month instead of recalculating the average at the end of each month cumulatively through the year

Chris Pfeiffer || Mob: +44 (0) 7949 215160 || E-Mail:  <mailto:chris@reporting4retail.com> chris@reporting4retail.com || Web:  <http://www.reporting4retail.com/> www.reporting4retail.com