3 Replies Latest reply: Sep 14, 2017 3:26 AM by Thabang Petje

# Max(MonthYear) and 6Months restriction

Hi Everyone,

I have create Date field from that I have calculated Create_Month Year like below but getting duplicate month year in the filters. Please see the image attached.

Month("Create Date") as "Create Month",

Year("Create Date") as "Create Year",

Num(Month("Create Date")) as "CreateNumMonth",

DATE("Create Date",'MMM-YY') AS Create_MonthYear,

Requirement is I need to show the max(MonthYear) rating

Max(Monthyear) used this expression -

Count({<[Overall Rating]={'Positive'},Create_MonthYear={'=\$(=Max(Create_MonthYear))'}>}[ID])

/

Count({<Create_MonthYear={'=\$(=Max(Create_MonthYear))'}>}[ID])

but I am getting null value.

• ###### Re: Max(MonthYear) and 6Months restriction

Try this?

Count({<[Overall Rating]={'Positive'},Create_MonthYear={"\$(=Max(Create_MonthYear))"}>}[ID])

/

Count({<Create_MonthYear={"\$(=Max(Create_MonthYear))"}>}[ID])

And for 6 months, Expression looks okay to me

• ###### Re: Max(MonthYear) and 6Months restriction

In place of the Create_MonthYear use Year amd NumMonth Fields in the SET analysis

=Count( {<[Overall Rating]={'Positive'}, [Create Year] ={"'\$(=Max([Create Year]))'"}, CreateNumMonth ={"'\$(=Max(CreateNumMonth))'"}   >} ID] )

/

Count({<[Create Year] ={"'\$(=Max([Create Year]))'"}, CreateNumMonth ={"'\$(=Max(CreateNumMonth))'"} >} [ID] )

• ###### Re: Max(MonthYear) and 6Months restriction

Hi,

Try this:

Count({<[Overall Rating]={'Positive'},Create_MonthYear={'\$(=MonthName(Max(Create_MonthYear)))'}>}[ID])

/

Count({<Create_MonthYear={'\$(=MonthName(Max(Create_MonthYear)))'}>}[ID])

Alternatively, you can use the solution given by Anand above.

The reason your expression was not working is that Max returns an int, therefore Max(Create_MonthYear) would return a number of the Create_MonthYear field but the Create_MonthYear acually returns a string. So I thing the fields are not evaluating because of that