Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
darrellbutler
Creator
Creator

Rolling twelve months in bar chart

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

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

7 Replies
Not applicable

Please try below expression.

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

Let me know how it goes.

Not applicable

Hi!,

See the TestAPP.qvw in the following link.

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

darrellbutler
Creator
Creator
Author

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 !

Not applicable

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
Luminary Alumni
Luminary Alumni

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
Partner - Champion
Partner - Champion

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
Creator
Creator
Author

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

Thanks once again.