Rolling twelve months in bar chart

[darrellbutler]

Hi, I want to show a chart with a dimension of MMM-YY (C_MonthYear) the expression should be a rolling twleve months sales i.e for Feb 2012 I want sales from Mar-2011 to Feb2012.

I'm using the following expression

=SUM({$< [C_MonthYear] = {">=$(=addmonths(C_MonthYear,-12)) <=$(=C_MonthYear)"}>} COLLECTION_LITRES)  however this returns no data has anybody got any advice on how to do this.

Many thanks.

[#2]

Please try below expression.

=SUM({$<[C_MonthYear]={">=$(=addmonths(max(C_MonthYear),-12)) <=$(=max(C_MonthYear))"}>} COLLECTION_LITRES)

Let me know how it goes.

[#3]

Hi!,

See the TestAPP.qvw in the following link.

http://community.qlik.com/message/187748#187748

[darrellbutler]

Hi, thanks for your help - this has the effect of showing the last twelve months. If you select Feb 2012 the graph shows Feb 2011 - to Feb 2012 across 12 bars.

What I want to do is show 12 months sales totalled in one bar. For example for feb 2012 bar i want to see the sum of Feb 2011-2012, Jan 2012 bar Jan 2011 - to Jan 2012 and so on.

Any help you can offer would be appreciated !

[#5]

Try Something Like This:

=(RangeSum(above(sum({$<MonthYear=,Month=,Year=,Date={"<=$(Variable1) >=$(Variable2)"}>}

Sales),0,12))

where:

Variable1==Max(Date)

Variable2==AddMonths(Max(Date),-23)

Dimension: MonthYear

Sort: Numeric Value - Ascending

Presentation Tab: Check the Chechbox Reversed

[jagan · Accepted solution]

Hi,

Write separate expression for each bar, if you need 12 bars then you write 12 expressions without any dimensions.

For example

Assume that C_MonthYear is January

For January:

=SUM({$<[C_MonthYear]={">=$(=addmonths(max(C_MonthYear),-12)) <=$(=max(C_MonthYear))"}>} COLLECTION_LITRES)

For December:

=SUM({$<[C_MonthYear]={">=$(=addmonths(max(C_MonthYear),-13)) <=$(=AddMonths(max(C_MonthYear), -1))"}>} COLLECTION_LITRES)

For November:

=SUM({$<[C_MonthYear]={">=$(=addmonths(max(C_MonthYear),-14)) <=$(=AddMonths(max(C_MonthYear), -2))"}>} COLLECTION_LITRES)

Try like this.

Hope this helps you.

Regards,

Jagan.

[CELAMBARASAN]

Hi,

     If you don't need 12 bars use the same expression without month or any dimension and i suggest to use the text in chart option to display the dimension text like Feb-2011 to Feb-2012 by using expression like

     = addmonths(max(C_MonthYear),-12) & 'to' &max(C_MonthYear).

Because your dimension value only split that to 12 bars.

Hope this helps

Celambarasan

[darrellbutler]

Many thanks for all your help guys - I think I've got enough here to progress.

Thanks once again.