Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Rolling 12 months in bar chart

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

5 Replies
Not applicable
Author

You could probably do set analysis for the expression like:

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

Anonymous
Not applicable
Author

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

Not applicable
Author

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)

Anonymous
Not applicable
Author

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

Not applicable
Author

Can you post a sample?