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

    Max(MonthYear) and 6Months restriction

    bhavani b

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

          • Re: Max(MonthYear) and 6Months restriction
            Anand Chouhan

            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
              Thabang Petje

              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