Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

amit_saini
Honored Contributor III

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

10 Replies
Not applicable

Re: Exclude current month from YTD Expression

Try this,

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

amit_saini
Honored Contributor III

Re: Exclude current month from YTD Expression

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

Thanks.
AS

Not applicable

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

Not applicable

Re: Exclude current month from YTD Expression

Try following in set expression

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

amit_saini
Honored Contributor III

Re: Exclude current month from YTD Expression

Sorry Taras not working.

Folks any  further suggestion????

Thanks,

AS

senpradip007
Valued Contributor III

Re: Exclude current month from YTD Expression

Try this

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

amit_saini
Honored Contributor III

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

jyothish8807
Honored Contributor II

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:

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

Regards

KC

Best Regards,
KC
amit_saini
Honored Contributor III

Re: Exclude current month from YTD Expression

Thanks for Help KC!

Still YTD is carrying march data.

Thanks,
AS

Community Browser