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

# YTD exclude current month

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

Thanks,

AS

• ###### Re: YTD exclude current month

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

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:

• ###### Re: YTD exclude current month

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

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

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.