topic Re: Chart Accumulation (with Dimensions) in QlikView

Chart Accumulation (with Dimensions)

Sat, 06 Aug 2011 23:33:33 GMT

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)

Sun, 07 Aug 2011 11:17:36 GMT

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)

Sun, 07 Aug 2011 11:24:01 GMT

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)

Sun, 07 Aug 2011 11:42:11 GMT

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)

Mon, 08 Aug 2011 15:57:04 GMT

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.