Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

crazyhorse66
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

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
Not applicable

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
Not applicable

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?