10 Replies Latest reply: Mar 17, 2015 6:32 AM by Harshal Patil

# Exclude current month from YTD Expression

Hi Folks ,

I'm trying to neglect current month form the YTD expression , below is my expression :

My Month field here is "POPER_NEW"

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

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

I tried something like below :

POPER_NEW={"(=max(POPER_NEW)-1)"}  , but its not working.

kindly help me here!'

Thanks,

AS

• ###### Re: Exclude current month from YTD Expression

Try this,

POPER_NEW={"\$(=max(POPER_NEW)-1)"}

• ###### Re: Exclude current month from YTD Expression

Thanks.
AS

• ###### Re: Exclude current month from YTD Expression

TRY THIS,

if(sum({<Date={">=\$(=YearStart(Date))<=\$(=Max(Date))"},Year={2015},

POPER_NEW-={"\$(=MONTH(TODAY()))"},Date=>}[Act Planned Production Hours]) = 0,

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

check the format of POPER_NEW with MONTH(TODAY()) with your fieldvalue & try...

• ###### Re: Exclude current month from YTD Expression

Harshal,

I tried this way:

=if(sum({<Date={">=\$(=YearStart(Date))<=\$(=Max(Date))"},Year={2015},POPER_NEW-={"\$(=Date(Monthstart(Today())),'DD.MMM.YYYY')"},Date=>}[Act Planned Production Hours]) = 0, avg({<Date={">=\$(=YearStart(Date))<=\$(=Max(Date))"},Year={2015},POPER_NEW-={"\$(=Date(MonthStart(Today())),'DD.MMM.YYYY')"},Date=>}[Act OEE]),

sum({<Date={">=\$(=YearStart(Date))<=\$(=Max(Date))"},Year={2015},POPER_NEW-={"\$(=Date(Monthstart(Today())),'DD.MMM.YYYY')"},Date=>}[Act OEE]*[Act Planned Production Hours])/sum({<Date={">=\$(=YearStart(Date))<=\$(=Max(Date))"},Year={2015},POPER_NEW-={"\$(=Date(MonthStart(Today())),'DD.MMM.YYYY')"},Date=>}[Act Planned Production Hours]))

But still not getting desired result. It should not include march month data in YTD.

Thanks,
AS

• ###### Re: Exclude current month from YTD Expression

Hi Amit

This part:

POPER_NEW-={"\$(=Date(MonthStart(Today())),'DD.MMM.YYYY')" will omit only the first day of current month, not the entire month.

Your expression is getting conflicted between two conditions.

({<Date={">=\$(=YearStart(Date))<=\$(=Max(Date))"},

over here it is considering till max date, you have to change it over here also to:

Regards

KC

• ###### Re: Exclude current month from YTD Expression

Thanks for Help KC!

Still YTD is carrying march data.

Thanks,
AS

• ###### Re: Exclude current month from YTD Expression

HI

POPER_NEW-={"\$(=Date(Monthstart(Today())),'DD.MMM.YYYY')"}

it will not properly mentioned always test it within text-box whether date is coming proper or not on which you gone perform action...

If you have POPER_NEW is in Date format then at script level create month field based on POPER_NEW

like ,

monthname(POPER_NEW) as POPER_NEW_Month,

POPER_NEW_Month-={"\$(=Monthname(Today()))"}

Just try now...

regards

• ###### Re: Exclude current month from YTD Expression

Try following in set expression

• ###### Re: Exclude current month from YTD Expression

Sorry Taras not working.

Folks any  further suggestion????

Thanks,

AS

• ###### Re: Exclude current month from YTD Expression

Try this

POPER_NEW={"<\$(=max(POPER_NEW))"}