topic Re: Calculating Year To Date Accumulation Expression in QlikView

Calculating Year To Date Accumulation Expression

Thu, 24 Jan 2013 14:51:37 GMT

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?

Re: Calculating Year To Date Accumulation Expression

Thu, 24 Jan 2013 19:42:49 GMT

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

Re: Calculating Year To Date Accumulation Expression

Thu, 24 Jan 2013 20:30:54 GMT

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.

Re: Calculating Year To Date Accumulation Expression

Thu, 24 Jan 2013 20:53:15 GMT

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

Best,

Matt

Re: Calculating Year To Date Accumulation Expression

nagaiank — Thu, 24 Jan 2013 20:54:24 GMT

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

Re: Calculating Year To Date Accumulation Expression

Fri, 25 Jan 2013 01:49:27 GMT

Were you able to resolve this issue?

Re: Calculating Year To Date Accumulation Expression

Tue, 29 Jan 2013 22:44:32 GMT

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

Re: Calculating Year To Date Accumulation Expression

Tue, 29 Jan 2013 22:47:44 GMT

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.

Re: Calculating Year To Date Accumulation Expression

nagaiank — Tue, 29 Jan 2013 23:32:15 GMT

Is the attached application meeting your requirements?

Re: Calculating Year To Date Accumulation Expression

Thu, 31 Jan 2013 20:25:52 GMT

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

Re: Calculating Year To Date Accumulation Expression

Thu, 31 Jan 2013 20:50:38 GMT

This still isn't working correctly. I think it is working for month 2 but not for any of the other months.

Re: Calculating Year To Date Accumulation Expression

Fri, 01 Feb 2013 02:02:42 GMT

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

Re: Calculating Year To Date Accumulation Expression

Fri, 01 Feb 2013 15:27:27 GMT

This works great! Thanks so much for your help.

Craig