5 Replies Latest reply: Jan 20, 2016 2:32 AM by jagan mohan rao appala RSS

    YTD exclude current month

    Amit Saini

      Hi Folks,

       

      I'm having below expression , which is working fine:

       

      if(sum([Act Planned Production Hours]) = 0, avg([Act OEE]),

      sum([Act OEE]*[Act Planned Production Hours])/sum([Act Planned Production Hours]))

       

       

      Need YTD expression by using above expression and  YTD should not include current month data.

       

      Calander fields are Year , Month= POPER_NEW.

       

      trying something like below:

       

      if(sum({$<Date={">=$(=YearStart(Date))<=$(=Max(Date))"},Year=,POPER_NEW=,Date=>}[Act Planned Production Hours]) = 0, avg({$<Date={">=$(=YearStart(Date))<=$(=Max(Date))"},Year=,POPER_NEW=,Date=>}[Act OEE]),

      sum({$<Date={">=$(=YearStart(Date))<=$(=Max(Date))"},Year=,POPER_NEW=,Date=>}[Act OEE]*[Act Planned Production Hours])/sum({$<Date={">=$(=YearStart(Date))<=$(=Max(Date))"},Year=,POPER_NEW=,Date=>}[Act Planned Production Hours]))

       

      Please suggest!

       

      Thanks,

      AS

        • Re: YTD exclude current month
          Prashant Sangle

          Hi,

           

          Instead of Max(Date) , You can use

          Date(Monthstart(Max(Date))-1) to get till previous monthend date

           

          Regards,

          • Re: YTD exclude current month
            Sunny Talwar

            Just focusing on this part -> {$<Date={">=$(=YearStart(Date))<=$(=Max(Date))"} may be I would add Date function here so that the format of date and the stuff on the right side of the equation are in same format -> {$<Date={">=$(=Date(YearStart(Date), 'FormatHere'))<=$(=Date(Max(Date), 'FormatHere))"}

             

            Next, if you are looking for up untill last month, may be this:

             

            {$<Date={">=$(=Date(YearStart(Date), 'FormatHere'))<=$(=Date(MonthEnd(AddMonths(Max(Date), -1)), 'FormatHere))"}

            • Re: YTD exclude current month
              Hirish V

              Hi,

               

              may be like this,

               

              For Month in expression,

               

              Month={'<$(=Max(Month(Date)))'}

               

              or

               

              POPER_NEW={'<$(=Max(POPER_NEW)'}

               

               

              Hope this helps,

              Hirish

              • Re: YTD exclude current month
                Manish Kachhia

                Try this..

                 

                =If(

                  SUM({$<Date={">=$(=Date(YearStart(Max(Date))))<=$(=Date(MonthEnd(Max(Date),-1)))"},Year=,POPER_NEW=,Date=>}[Act Planned Production Hours]) = 0,

                  avg({$<Date={">=$(=Date(YearStart(Max(Date))))<=$(=Date(MonthEnd(Max(Date),-1)))"},Year=,POPER_NEW=,Date=>}[Act OEE]),

                  sum({$<Date={">=$(=Date(YearStart(Max(Date))))<=$(=Date(MonthEnd(Max(Date),-1)))"},Year=,POPER_NEW=,Date=>}[Act OEE]*[Act Planned Production Hours])

                  /

                  sum({$<Date={">=$(=Date(YearStart(Max(Date))))<=$(=Date(MonthEnd(Max(Date),-1)))"},Year=,POPER_NEW=,Date=>}[Act Planned Production Hours])

                  )

                • Re: YTD exclude current month
                  jagan mohan rao appala

                  HI,

                   

                  Try this

                   

                  if(sum({$<Date={">=$(=YearStart(Today()))<=$(=MonthEnd(Today(), -1))"},Year=,POPER_NEW=,Date=>}[Act Planned Production Hours]) = 0, avg({$<Date={">=$(=YearStart(Today()))<=$(=MonthEnd(Today(), -1))"},Year=,POPER_NEW=,Date=>}[Act OEE]),

                  sum({$<Date={">=$(=YearStart(Today()))<=$(=MonthEnd(Today(), -1))"},Year=,POPER_NEW=,Date=>}[Act OEE]*[Act Planned Production Hours])/sum({$<Date={">=$(=YearStart(Today()))<=$(=MonthEnd(Today(), -1))"},Year=,POPER_NEW=,Date=>}[Act Planned Production Hours]))

                   

                  Hope this helps you.

                   

                  Regards,

                  Jagan.