Exclude current month from YTD Expression

[amit_saini]

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

[#2]

Try this,

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

[amit_saini]

Thanks Harshal for your reply , still issue getting blank'-' values.

Thanks.
AS

[#4]

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

[#5]

Try following in set expression

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

[amit_saini]

Sorry Taras not working.

Folks any  further suggestion????

Thanks,

AS

[senpradip007]

Try this

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

[amit_saini]

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

[jyothish8807]

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:

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

Regards

KC

[amit_saini]

Thanks for Help KC!

Still YTD is carrying march data.

Thanks,
AS