Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
crazyhorse66
New Contributor II

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

Rolling 12 months in bar chart

You could probably do set analysis for the expression like:

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

crazyhorse66
New Contributor II

Rolling 12 months in bar chart

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

Rolling 12 months in bar chart

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)

crazyhorse66
New Contributor II

Rolling 12 months in bar chart

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

Rolling 12 months in bar chart

Can you post a sample?