12 Replies Latest reply: Feb 1, 2013 10:27 AM by cjcamp22

# 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?

• ###### Re: Calculating Year To Date Accumulation Expression

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

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

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

Best,

Matt

• ###### Re: Calculating Year To Date Accumulation Expression

Were you able to resolve this issue?

• ###### Re: Calculating Year To Date Accumulation Expression

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?

Craig

• ###### Re: Calculating Year To Date Accumulation Expression

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

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

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

• ###### Re: Calculating Year To Date Accumulation Expression

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.