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

[PrashantSangle]

Hi,

Instead of Max(Date) , You can use

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

Regards,

[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))"}

[HirisH_V7]

Hi,

may be like this,

For Month in expression,

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

or

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

Hope this helps,

Hirish

[MK_QSL]

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

  )

[jagan]

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.