Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
amit_saini
Master III
Master III

YTD exclude current month

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,

AS

5 Replies
PrashantSangle

Hi,

Instead of Max(Date) , You can use

Date(Monthstart(Max(Date))-1) to get till previous monthend date

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sunny_talwar

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))"}

HirisH_V7
Master
Master

Hi,

may be like this,

For Month in expression,

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

or

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

Hope this helps,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
MK_QSL
MVP
MVP

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

  )

jagan
Luminary Alumni
Luminary Alumni

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.