Cumulative Average Calculations

[#1]

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

[#2]

Hello,

Can you upload your qvw file ?

Thanks,

Amand

[#3]

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

[#4]

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

[#5]

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

[#6]

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

[#7]

Hi,

See this steps to create the document.

Script:

TMP:

LOAD * INLINE [

    Month, Value

    01, 100

    02, 150

    03, 125

    04, 95

    05, 100

    06, 130

];

Create a Straight Chart with Month as Dimension

and add the expression:

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

Regards

[swuehl · Accepted solution]

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

[#9]

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

[#10]

Hi Stefan – that’s it! Brilliant and many thanks

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