MTD,YTD

[#1]

Somebody suggest me a good approach to construct MTD YTD,

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

[Sokkorn]

Hi,

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

Regards,

Sokkorn

[#3]

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,

[#4]

Hi,

Best approach for MTD:

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

Regards

Shweta

[er_mohit]

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)

[#6]

HI Mohit,

Could you  please tell me good approach to calculate LQTD

Regards

Shweta