Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

darrellbutler
Contributor

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
MVP & Luminary
MVP & Luminary

Rolling twelve months in bar chart

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.

7 Replies
Not applicable

Re: Rolling twelve months in bar chart

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

Rolling twelve months in bar chart

Hi!,

See the TestAPP.qvw in the following link.

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

darrellbutler
Contributor

Rolling twelve months in bar chart

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

Rolling twelve months in bar chart

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

MVP & Luminary
MVP & Luminary

Rolling twelve months in bar chart

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.

Rolling twelve months in bar chart

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
Contributor

Rolling twelve months in bar chart

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

Thanks once again.