5 Replies Latest reply: Sep 27, 2016 5:01 AM by Varsha Vig RSS

    Max month minus 1

    Rhona Corcoran

      Hi The expression below is giving me the max YearMonth but I cannot get it to work for the Max YearMonth -1

       

      Any help appreciated

       

      if(isnull(num((((Avg({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth))"}>}ActualValue)))), vNumber)),Null(),

       

       

       

       

      num((((sum({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth))"}>}ActualValue)))), vNumber))

        • Re: Max month minus 1
          ioannis giakoumakis

          What is the YearMonth format? 201606 for example?

          • Re: Max month minus 1
            Anil Babu Samineni

            I recommend you to create Variable for that

             

            For prev month you can try this too

             

            if(isnull(num((((Avg({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth)-1)"}>}ActualValue)))), vNumber)),Null(),

            num((((sum({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Max({<[HRPrimaryCare]={7}>}YearMonth)-1)"}>}ActualValue)))), vNumber))

             

            OR

             

            if(isnull(num((((Avg({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Addmonths(Max({<[HRPrimaryCare]={7}>}YearMonth)),-1)"}>}ActualValue)))), vNumber)),Null(),

            num((((sum({$<HRPrimaryCare={7},ReferredYYYY,YearMonth={"=$(#=Addmonths(Max({<[HRPrimaryCare]={7}>}YearMonth)),-1)"}>}ActualValue)))), vNumber))

             

            Note: AddMonth(Expression, -1) will gives the previous month Data. If not, better to offer you logic if you shared the application

            • Re: Max month minus 1
              Varsha Vig

              addmonths

              • Re: Max month minus 1
                Sunny Talwar

                How about this:

                 

                Avg({$<HRPrimaryCare = {7}, ReferredYYYY, YearMonth = {"$(=MonthName(AddMonths(Max({<[HRPrimaryCare] = {7}>} YearMonth), -1)))"}>} ActualValue)

                 

                and for this month, you might be able to simplify your expression to this

                 

                Avg({$<HRPrimaryCare = {7}, ReferredYYYY, YearMonth = {"$(=MonthName(AddMonths(Max({<[HRPrimaryCare] = {7}>} YearMonth), 0)))"}>} ActualValue)