Getting Last 3 months on Year Selection

[Anonymous]

Hi all,

Consider Year field and i have 3 years 2015,2016,2017

Month field has all the 12 months for those 3 years (2017 has only January)

If i select Year 2017, it should display max month from that year and last 2 months (i.e)

2017 Jan

2016 Dec

2016 Nov.

The issue is if i select 2017

Max month is Jan

Max month -1 is 0 ( it should be 2016 dec)

Max month -2 is -1 ( it should be 2016 nov)

How do i get?

2017 Jan

2016 Dec

2016 Nov.

Thanks..

[sunny_talwar]

In a particular chart or throughout the dashboard?

For a chart, I would suggest using set analysis

For the dashboard, you can may be use triggers (but use them with caution)

[Anonymous]

In a particular chart.

Could you help me with Set analysis expression for this?

Thanks.

[sunny_talwar · Accepted solution]

Sure, what are your date and time related dimensions. Let say you have a field called Month Year which is created in the script like this

LOAD Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear

Your set analysis should look like this:

{<MonthYear = {"$(='>=' & Date(MonthStart(Max(MonthYear), -2), 'MMM-YYYY') & '<=' & Date(MonthStart(Max(MonthYear), 0), 'MMM-YYYY'))"}>}

Also, look here:

Dates in Set Analysis

[Anonymous]

Hi sunny,

Thanks for your help.

I have also  included,

Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear

in my script

Can you please explain me how this expression works?

[sunny_talwar]

It takes your date field for each row, find its Month starting date and then assign it a format of MMM-YYYY (Jan-2017 for example)

[Anonymous]

Sorry. i asked about the set analysis expression.

{<MonthYear = {"$(='>=' & Date(MonthStart(Max(MonthYear), -2), 'MMM-YYYY') & '<=' & Date(MonthStart(Max(MonthYear), 0), 'MMM-YYYY'))"}>}

[sunny_talwar]

Showing MonthYear which is >= Max(MonthYear) - 2 months and <= Max(MonthYear)

[Anonymous]

Thanks for your help...

[sunny_talwar]

No problem at all