topic Re: YTD exclude current month in QlikView

YTD exclude current month

amit_saini — Wed, 20 Jan 2016 07:12:06 GMT

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

Please suggest!

Thanks,

Re: YTD exclude current month

PrashantSangle — Wed, 20 Jan 2016 07:17:03 GMT

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

sunny_talwar — Wed, 20 Jan 2016 07:18:01 GMT

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:

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

Re: YTD exclude current month

HirisH_V7 — Wed, 20 Jan 2016 07:18:23 GMT

Hi,

may be like this,

For Month in expression,

Month={'<$(=Max(Month(Date)))'}

POPER_NEW={'<$(=Max(POPER_NEW)'}

Hope this helps,

Hirish

Re: YTD exclude current month

MK_QSL — Wed, 20 Jan 2016 07:31:53 GMT

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

jagan — Wed, 20 Jan 2016 07:32:18 GMT

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.