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

[bhavvibudagam]

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,

[Anonymous]

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!

[lironbaram]

hi

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)

[sunny_talwar · Accepted solution]

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)

[bhavvibudagam]

Thank you so much its working