topic Rolling 12 months in bar chart in QlikView

Rolling 12 months in bar chart

Anonymous — Tue, 04 Oct 2011 13:33:53 GMT

Hi - thanks in advance for any help.

I have a bar chart which has this as the dimension:

planmonth(date) - so displays Jan 2011 - Oct 2011 when 2011 is selected - fine.

I have this as an expression:

sum([Valued Plan])

The chart works fine in that it shows the previous months in the year selected or all months/years if no date selected, but needs to go back further.

Need this chart to always show the last 12 months - so as of now Oct 2011 back to Nov 2010

When viewed in say March 2012 will show Mar 2012 back to Apr 2011

Hope this makes sense, thanks again

Rolling 12 months in bar chart

Tue, 04 Oct 2011 13:45:14 GMT

You could probably do set analysis for the expression like:

sum({$< planmonth = {"<=$(=Max(planmonth))"},{">=$(=Max(planmonth)-12)"}>} [Valued Plan])

Rolling 12 months in bar chart

Anonymous — Tue, 04 Oct 2011 14:03:00 GMT

Thanks for the response

I tried that but failed to load any data.

I replaced planmonth with my planmonth(date) which is [Plan Month Date]

so:

sum({$< [Plan Month Date] = {"<=$(=Max([Plan Month Date]))"},{">=$(=Max([Plan Month Date])-12)"}>} [Valued Plan])

is that correct? missing a comma, bracket, quote? Thanks

Rolling 12 months in bar chart

Tue, 04 Oct 2011 14:13:31 GMT

Ok, is your field planmonth(date) return a date, a month, or a year?

It had extra brackets , so this will work, but I do not think it is what you are looking for.

sum({$< [Plan Month Date]= {"<=$(=Max([Plan Month Date]))",">=$(=Max([Plan Month Date])-12)"}>}[Valued Plan])

Try

sum({$< [Plan Month Date]= {"<=$(=Max([Plan Month Date]))",">=$(=monthstart(Max([Plan Month Date])-365))"}>}[Valued Plan])

To test the values being returned for date just create a list box with expression Max([Plan Month Date]) and another list box with expression monthstart(Max([Plan Month Date])-365)

Rolling 12 months in bar chart

Anonymous — Tue, 04 Oct 2011 14:26:23 GMT

The field is planmonth so Jan 2011, Feb 2011, Mar 2011 etc

Tried those 2 expressions - both return all data available rather than last 12 months

Rolling 12 months in bar chart

Tue, 04 Oct 2011 14:32:24 GMT

Can you post a sample?