4 Replies Latest reply: Aug 8, 2011 11:57 AM by Srini r

# Chart Accumulation (with Dimensions)

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...

• ###### Re: Chart Accumulation (with Dimensions)

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

• ###### Re: Chart Accumulation (with Dimensions)

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.

• ###### Re: Chart Accumulation (with Dimensions)

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.

• ###### Re: Chart Accumulation (with Dimensions)

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.