Only show last 12 months on a chart

[jgreen95]

Hi,

I have spent ages looking at this and cant find a way to make it work. I have a number of graphs/tables i only want to show a set amount of months for (normally last 12)

Currently i am using this autocalendar function to display the month in my data: 

[Inspection Logged-Notification Created Date.autoCalendar.YearMonth]

anyone got any ideas how to do this? 

[jyothish8807]

Hi Jgreen,

May be this:

Use this as your calculated dimension

If([Inspection Logged-Notification Created Date.autoCalendar.YearMonth] >= AddMonths(max([Inspection Logged-Notification Created Date.autoCalendar.YearMonth]), -12), Date)

Just make sure the format of the filed and Addmonths outcome is same.

Br,

KC

[jgreen95]

Thanks KC but that just seems to give me an error

[jyothish8807]

try this,

If([Inspection Logged-Notification Created Date.autoCalendar.YearMonth] >= AddMonths(max([Inspection Logged-Notification Created Date.autoCalendar.YearMonth]), -12), [Inspection Logged-Notification Created Date.autoCalendar.YearMonth])

Br,

KC

[jgreen95]

already tried that and no luck, would it be best to do the calculation before the autocalendar conversion to Month? or will that not effect it

[jgreen95]

I tried just doing this:

=If([Inspection Logged-Notification Created Date.autoCalendar.Date] >= AddMonths(max([Inspection Logged-Notification Created Date.autoCalendar.Date]), -12), [Inspection Logged-Notification Created Date.autoCalendar.Date])

so no month calculation, the expression is fine but it says it is an "invalid dimension"

[jyothish8807]

Hi Jgreen,

Can you please check what is the value your are getting when you put below expression in a text box ? The format of the expression should be same as your date field.

 AddMonths(max([Inspection Logged-Notification Created Date.autoCalendar.Date]), -12)

if not, then simply add date( exp,'your date format') and give it a try.

Else please share a sample so it will be helpful to troubleshoot.

You can implement this logic after creating the master calendar.

Br,

KC

[jgreen95 · Accepted solution]

Thanks KC I actually managed to solve it myself, created a variable for CurrentMonth-12 used the month start function

then used the below calculated dimension and excluded null values

=
If([Inspection Logged-Notification Created Date.autoCalendar.Date]>=[CurrentMonth-12],([Inspection Logged-Notification Created Date.autoCalendar.YearMonth]),null)