How to create an accumulation for every 12 months in 3 years graph

[HirisH_V7]

Hi Qlikers,

I want to create an accumulation for every 12 months in Combo Chart such that for example,

2012- Jan -Dec

2013- Jan -Dec

2014- Jan -Dec

for yearly comparison.

-Thanks In Advance,

Please find attachment,

Hirish

[Gysbert_Wassenaar]

That's rangesum(above(Sum(Sales),0,MonthNum)) which he said doesn't work for him

[sunny_talwar]

Hahahaha not sure what else he is looking for.

[stevedark]

If it's a chart like petter.skjolden shows, with a line per year accumulated each month, then the data model accumulation like I describe in my blog post works well.

You need to create a table which joins each month to an AccMonth dimension, so you would have:

Month,AccMonth

Jan,Jan

Jan,Feb

Feb,Feb

Jan,Mar

Feb,Mar

Mar,Mar

etc.

So each accumulated month value includes all previous months in the year.

This can be built in a loop, something like:

for iAccMonth = 1 to 12

   for iMonth = 1 to iAccMonth

      LOAD

         Month(MakeDate(2000,$(iMonth), 1)) as Month,

         Month(MakeDate(2000,$(iAccMonth), 1)) as AccMonth

      AUTOGENERATE(1);

   next

next

You then just need to use the AccMonth dimension in place of the Month dimension.

The beauty of this, over using the RangeSum oc Accumulation Tick Box is that you can pick a range of AccMonths on the chart and the values will still be accumulated from the start of the year - rather than the start of what is visible in the chart.

Steve

[sunny_talwar]

Steve Dark:

The beauty of this, over using the RangeSum oc Accumulation Tick Box is that you can pick a range of AccMonths on the chart and the values will still be accumulated from the start of the year - rather than the start of what is visible in the chart.

Steve

To this point Steve, lets say that there is one dimension called product and we do want the chart to adjust based on selection of a particular product. Would the solution that you proposed will adjust to the selection of a particular product?

Thanks and regards,

Sunny

[stevedark]

Hi Sunny,

Yes, associations with other tables and dimensions will still work fine.

I've done this sort of accumulation with many different accumulation types

in one app (rolling 12 months, YTD etc) and it tends to stack up well.

See the blog post in the link above for more details and example code.

Steve

[sunny_talwar]

Thanks Steve. I will take a look at your blog post