Chart Accumulation (with Dimensions)

[#1]

Hi

I am trying to create a chart that has bars for monthly sales (the chart has the primary dimension Month and the secondary dimension Year). Also, I want the same chart to show the accumulated sales as a line (but true to the dimensions, that is, QlikView should not accumulate for all years, but create accumulated sales per month for each year).

I have tried to setup an example of what I want, and how I have tried to solve the problem... Could anyone take a look at the attached file and help me figure out what is going on, and how I best solve the problem? I really don't understand why my set analysis is not working...

Thanks

Simon

Excel Example

PS: I updated this discussion with an Excel file of what the chart should look like...

[#2]

Hello Simon,

take a look at the chart I derived from your first guess. May be you want to replace my hardcoded years with a variable.

Regards, Roland

[#3 · Accepted solution]

Hi Simon,

Good Attempt of Set Analysis. But your expression is wrong. That's why you get error result.

Sum({1<Year={'=Max(Year)-1'}>}Sales) - Your expression

    Sum({<Year={$(=Max(Year)-1)}>}Sales) - Correct Expression

Sum({1<Year={'=Max(Year)'}>}Sales) - Your Expression

    Sum({<Year={$(=Max(Year))}>}Sales) - Correct Expression.

The reason i removed 1 is, it will exclude the selection.

Try this. It is working.

Regards,

Srini.

[#4]

Hi Roland & Srinivasan

Thanks for your replies... Both solutions were good. I didn't even think to try an If-expression. But I think I will go with Srinivasan's solution as I will need to discard any selections, and keep the '1'. I don't understand why I need both an equal sign and $() inside my set analysis..? Shouldn't the equal sign be enough..? Well, it is working, so I will just remember to use your syntax, Srinivasan.

Thanks for helping out...

// Simon

PS: I have attached my final solution, which also cuts off accumulation for months, which are not present as discussed elsewhere in the community.

[#5]

Hi Simon,

To answer you question "why I need both an equal sign and $() inside my set analysis..?"

     In Set analysis the expression inside set should be within $ i.e., "$(=Max(Year)-1)".

     The reason for equal sign inside the $ is it compute the expression and $ will return the value.

I hope this will be clear for you.

Regards,

Srini.