Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You could probably do set analysis for the expression like:
sum({$< planmonth = {"<=$(=Max(planmonth))"},{">=$(=Max(planmonth)-12)"}>} [Valued Plan])
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
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)
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
Can you post a sample?