Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating Year To Date Accumulation Expression

I am trying to show average cost per member per month in a bar chart and I want to show this as a year to date figure.  For example, for March, I would want an expression that would show this:

(January Cost + February Cost + March Cost) / (January Members + February Members + March Members)

I see where I can click 'Full Accumulation' but this just calculates it this way:

(January Cost / January Members) + (February Cost / February Members) + (March Cost / March Members)

So I essentially want to change the order of operations and I am guessing there is a way to do this in the expression formula.  Does anyone have an idea?

1 Solution

Accepted Solutions
Not applicable
Author

Here you go, works now, this time I put it through a more vigorous qa process;)

I've used the rangesum function to do it, quite useful, please find a good explanation here:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/12/accumulative-sums

Qvw is attached.

Best,

Matt

View solution in original post

12 Replies
Not applicable
Author

Hi,

Try something like this:

sum({<month = {'Jan','Feb', 'Mar'}>}Cost)/count({<month = {'Jan','Feb', 'Mar'}>}Memberhsip)

Substitute the correct field names for Cost, Membership (I assume you have a member id you can count on) and month.

Hope that helps.

Matt

Not applicable
Author

Not sure that will get me what I want exactly.  This is a bar chart with the months along the x-axis.  I need one expression to handle all months.  I was using Jan, Feb, and March as an example.

So for Jan, the calculation will be the same as monthly calc.

For Feb, need (January Cost + February Cost) / (January Members + February Members).

For Mar, formula is in the first post.

etc. etc.... until you are at you get to December.

Any way to change that formula so it looks at all months including and prior to the current month?

Thanks.

Not applicable
Author

ok, see attached, i think that's more what your looking for.

Best,

Matt

nagaiank
Specialist III
Specialist III

Here is a sample application. Hope this does what you want to do.

Not applicable
Author

Were you able to resolve this issue?

Not applicable
Author

Hi Matt,

Your answer should be what I want I need, but the formula for 'Accum. Cost Member' is not working correctly.  It is still only calculating the monthly statistic.  Hopefully it is something very minor that needs to be changed in your QVW to fix this.  Can you find how to fix it?

Thanks for your help!

Craig

Not applicable
Author

This is just the monthly figure.  I want to do:  Sum(Jan Cost + Feb Cost)/Sum(Jan Mbrs + Feb Mbrs).  See Mattsies QVW as it is close but the formula for Accum. Cost Member is still not quite right.

Thanks.

nagaiank
Specialist III
Specialist III

Is the attached application meeting your requirements?

Not applicable
Author

Hi Craig,

Please find solutoin attached. I didn't realize the accumulated average cost did not calculate correctly. Still not sure why that is since the fields are cumalitve.

I used set analysis as opposed to qlikview's cumulative function to calculate it this time and it works.

Hope that helps.

Matt