Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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 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.
ok, see attached, i think that's more what your looking for.
Best,
Matt
Here is a sample application. Hope this does what you want to do.
Were you able to resolve this issue?
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
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.
Is the attached application meeting your requirements?
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