topic Re: Getting Last 3 months on Year Selection in QlikView

Getting Last 3 months on Year Selection

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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..

Re: Getting Last 3 months on Year Selection

sunny_talwar — Fri, 13 Jan 2017 12:27:40 GMT

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)

Re: Getting Last 3 months on Year Selection

Anonymous — Fri, 13 Jan 2017 12:33:25 GMT

In a particular chart.

Could you help me with Set analysis expression for this?

Thanks.

Re: Getting Last 3 months on Year Selection

sunny_talwar — Fri, 13 Jan 2017 12:40:02 GMT

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

Re: Getting Last 3 months on Year Selection

Anonymous — Fri, 13 Jan 2017 13:55:22 GMT

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?

Re: Getting Last 3 months on Year Selection

sunny_talwar — Fri, 13 Jan 2017 13:57:51 GMT

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)

Re: Getting Last 3 months on Year Selection

Anonymous — Fri, 13 Jan 2017 14:51:59 GMT

Sorry. i asked about the set analysis expression.

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

Re: Getting Last 3 months on Year Selection

sunny_talwar — Fri, 13 Jan 2017 14:57:35 GMT

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

Re: Getting Last 3 months on Year Selection

Anonymous — Fri, 13 Jan 2017 15:08:32 GMT

Thanks for your help...

Re: Getting Last 3 months on Year Selection

sunny_talwar — Fri, 13 Jan 2017 15:10:27 GMT

No problem at all