topic Re: MTD,YTD in QlikView

MTD,YTD

Wed, 05 Jun 2013 06:52:14 GMT

Somebody suggest me a good approach to construct MTD YTD,

the frequency of records m having is more than 10 lac records..

Re: MTD,YTD

Sokkorn — Wed, 05 Jun 2013 06:58:29 GMT

Hi,

You share best approach in your previouse post already. Please close this thread to avoid duplicate.

Regards,

Sokkorn

Re: MTD,YTD

Wed, 05 Jun 2013 07:01:07 GMT

YTD.

There is many different ways to do it.

You could make a YearStart and then keep it within today

MTD:

Same approach. Make a MonthStart and keep your expression within today.

So example Date= '>=MonthStart(date) <= Today()'

Or you could create a master calendar like this:

TEMP:

LOAD

num(min(OrderDate)) AS MinDate,

num(max(OrderDate)) AS MaxDate

RESIDENT

OrderDetails;

LET vMinDate = peek('MinDate', 0, 'TEMP');

LET vMaxDate = peek('MaxDate', 0, 'TEMP');

DROP TABLE TEMP;

DateIsland:

LOAD

date($(vMinDate) + rowno() - 1) AS D,

year($(vMinDate) + rowno() - 1) AS Y,

month($(vMinDate) + rowno() - 1) AS M,

date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MY

AUTOGENERATE

vMaxDate - vMinDate + 1;

And then you could create some flags:

inyeartodate(D, $(vToday), 0) * -1 AS CYTD,

inmonthtodate(D, $(vToday), 0) * -1 AS CMTD,

Re: MTD,YTD

Wed, 12 Mar 2014 11:10:21 GMT

Hi,

Best approach for MTD:

sum({<Year={$(=Max(Year))},Date={'>=$(=monthstart(max(Date)))<=$(=max(Date))'}>}[Sales Amount])

Regards

Shweta

Re: MTD,YTD

er_mohit — Wed, 12 Mar 2014 11:25:27 GMT

For front end side

YTD

sum({<Year={'$(=Max(Year)'},Date={'>=$(vStartDate)<=$(vEndDate)'},Quarter=>}Amount)

here my two variable vStartDate =MonthStart(Date(min(Date)))

vEndDate =Date(Max(Date))

MTD

sum({<Year={'$(=Max(Year)'},Date={'>=$(=MonthStart(max(Date))) <=$(=max(Date))'},Quarter=>}Amount)

Re: MTD,YTD

Wed, 12 Mar 2014 12:22:32 GMT

HI Mohit,

Could you please tell me good approach to calculate LQTD

Regards

Shweta