topic Re: Set analysis expression for max(year) and max(month) in QlikView

Set analysis expression for max(year) and max(month)

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

Hi,

I have one straight table with Hour and DOW dimensions.My requirement is by default need to show the max(year) and max(month) data.

After user selection of particular year and particular month need to show that corresponding month data.At a time it need to show only one year and one month data.I have used the below expression but only with year its working by adding max(month) condition it does not show anything.What wrong in the below expression.

=Count({<CDTS_Year={$(=Max(CDTS_Year))},CDTS_Month={$(=Max(CDTS_Month))},CALL_SOURCE={'ANI/ALI','PHONE','WALK-IN'}>}EID)

Thanks,

Re: Set analysis expression for max(year) and max(month)

Anonymous — Thu, 18 Aug 2016 07:01:14 GMT

Hi Bhavvi,

Max Month, if you have more than one year is 12.

You have to select max month of max year. You can use MonthName function on script and get max(MonthName) or use this set expression:

{$<CDTS_Year= {$(=max(CDTS_Year))}, CDTS_Month= {"=$(=max({<Year={$(=max(CDTS_Year))}>} CDTS_Month))"}>}

Regards!

Re: Set analysis expression for max(year) and max(month)

lironbaram — Thu, 18 Aug 2016 07:05:00 GMT

in your case for example let say you want to display aug-2016

so max year will return 2016

but max month calculated alone will probably return December

so there are 2 solution :

1. create a YearMonth field and use it in the set analysis

2. use this expression

=Count({<CDTS_Year={$(=Max(CDTS_Year))},CDTS_Month={$(=Max({<CDTS_Year={$(=Max(CDTS_Year))}>}CDTS_Month))},CALL_SOURCE={'ANI/ALI','PHONE','WALK-IN'}>}EID)

Re: Set analysis expression for max(year) and max(month)

sunny_talwar — Thu, 18 Aug 2016 07:19:11 GMT

In addition to above to suggestions, I would also check if CDTS_Monthis a field with Numerical formatting or text formatting. For that what I mean is, does CDTS_Month contains 1,2, 3, 4... or Jan, Feb, Mar, Apr....If it later is true, I would suggest something like this in the scrip:

LOAD CDTS_Month,

Num(CDTS_Month) as CDTS_Month_Num

....

Now use this:

=Count({<CDTS_Year={$(=Max(CDTS_Year))},CDTS_MonthNum={$(=Max({<CDTS_Year={$(=Max(CDTS_Year))}>}CDTS_MonthNum))},CALL_SOURCE={'ANI/ALI','PHONE','WALK-IN'}>} EID)

Re: Set analysis expression for max(year) and max(month)

bhavvibudagam — Thu, 18 Aug 2016 07:29:42 GMT

Thank you so much its working