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




            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


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