topic MTD And YTD in QlikView

MTD And YTD

abhaysingh — Sat, 14 Mar 2015 12:00:16 GMT

=sum({< [Posting Key] = {'XY'}, [Document Type] = {'IND'} >} [Amount in local Currency])

sum({< [Posting Key] = {'AV'}, [Document Type] = {'CNA'} >} [Amount in local Currency])

How to Write MTD and YTD expression in this ..

Time dimension fields are

Date, Month, Year

Thanks In advance

rahulsud007 — Sat, 14 Mar 2015 13:02:51 GMT

Hi Abhay, I think this expression may work for you YTD:-

Sum({<[Posting Key] = {'XY'}, [Document Type] = {'IND'} ,Date={'>=$(=Num(YearStart(Max(Date))))<=$(=Max(Date))'}>} Amount in local Currency)

and For MTD

Sum({<[Posting Key] = {'XY'}, [Document Type] = {'IND'} ,Date={'>=$(=(MonthStart(Max(Date))))<=$(=Max(Date))'}}>}Amount in local Currency)

Sat, 14 Mar 2015 13:33:55 GMT

Hi,

Try like this.

MTD,

sum({<Date = {$(=Max(Date) - 12)}, Year = {$(=Max(Year)-1)}, Month = >}[Amount in local Currency] )

YTD,

sum({<Date = {$(=Max(Date))}, Year = {$(=Max(Year))}, Month = >}[Amount in local Currency] )

maxgro — Sat, 14 Mar 2015 13:41:50 GMT

example for YTD

=sum({$ <[Posting Key] = {'XY'}, [Document Type] = {'IND'}, Date={">=$(=yearstart(today()))<=$(=today())"}>} [Amount in local Currency])

and MTD

=sum({$ <[Posting Key] = {'XY'}, [Document Type] = {'IND'}, Date={">=$(=monthstart(today()))<=$(=today())"}>} [Amount in local Currency])