Max(MonthYear) and 6Months restriction

[bhavvibudagam]

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.

[Anil_Babu_Samineni]

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

[its_anandrjs]

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] )

[Anonymous]

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