topic Exclude current month from YTD Expression in QlikView

Exclude current month from YTD Expression

amit_saini — Sat, 14 Mar 2015 05:06:31 GMT

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,

Re: Exclude current month from YTD Expression

Sat, 14 Mar 2015 05:13:57 GMT

Try this,

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

Re: Exclude current month from YTD Expression

amit_saini — Sat, 14 Mar 2015 05:44:11 GMT

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

Thanks.
AS

Re: Exclude current month from YTD Expression

Sat, 14 Mar 2015 05:56:20 GMT

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

Sat, 14 Mar 2015 06:19:07 GMT

Try following in set expression

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

Re: Exclude current month from YTD Expression

amit_saini — Tue, 17 Mar 2015 04:32:23 GMT

Sorry Taras not working.

Folks any further suggestion????

Thanks,

Re: Exclude current month from YTD Expression

senpradip007 — Tue, 17 Mar 2015 05:04:27 GMT

Try this

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

Re: Exclude current month from YTD Expression

amit_saini — Tue, 17 Mar 2015 06:59:42 GMT

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

jyothish8807 — Tue, 17 Mar 2015 07:14:39 GMT

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

Re: Exclude current month from YTD Expression

amit_saini — Tue, 17 Mar 2015 09:39:27 GMT

Thanks for Help KC!

Still YTD is carrying march data.

Thanks,
AS

Re: Exclude current month from YTD Expression

Tue, 17 Mar 2015 10:30:59 GMT

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