YTQ, QTD, MTD and WTD

[CELAMBARASAN]

Year, Quarter, Month and Week To Date are the common analysis that I seen many applications. I will share the expression to do here

First to do this your data model should have the DateField in number format by applying floor

Similar to this

Floor(DateField) AS DateNum //it will gives you one whole number to represent date

YTD - Year To Date

A date should be selected and it will look for the Starting date of the year to the selected date.

Ex: date selected is 21-03-2014 then YTD is 01-01-2014 to 21-03-2014

Expression would be

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

QTD- Quarter to Date

In the place of year use Quarter

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(QuarterStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

MTD- Month to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

WTD- Week to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(WeekStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

if you want you can set a variable value as 'Year', 'Month', 'Quarter', 'Week', lets say vToDate and go with single chart and single expression

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num($(=vToDate& 'Start(Max(DateNum))')))<=$(=Max(DateNum))"}>} Sales)

Will keep your expression simple

Regards,

Celambarasan

[#2]

I am also running into this issue.

I want a KPI to just show current year's YTD.

IF someone selects a date on the dashboard then I'd like to show YTD for selected date, but for default just Current YTD.

Or even a KPI that ALWAYS shows current years' YTD and I could just call it "Current YTD Sales", etc...

[CELAMBARASAN]

Hi Josh,

If you need Current Years YTD then make a change in set analysis instead of Max(DateNum)  use Today()

try the below one

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Today())))<=$(=Today())"}>} Sales)

[Anil_Babu_Samineni]

Nice Article, I had edited from Month to Date to Week to Date for WTD(Typo Mistake)

[Anil_Babu_Samineni]

Scotly, those coming from Master Calendar

MC:

Month(DateField) as Month,

Year(DateField) as Year,

Celi(Month(DateField)/3) as Quarter,

Week(DateField) as Week,

DateField is simple Date field. That is the reason he is using those. So, finally i want to say one thing which is You have one date field and then you want to split those into Fiscal year calendar

[ahaahaaha]

Good information. Thank you.

[vireshkolagimat]

Hi, I tried to create the metrics with above method but getting the same result in all the metrics.

any idea what is wrong?

thanks

Viresh

Below are my expressions.

YTD -

sum({<Year=,Month=, Quarter=, [Order Date]=,DateNum={">=$(=Num(QuarterStart(max(DateNum)))))

<=$(=max(DateNum))"}>}Sales)

QTD -

sum({<Year=,Month=, Quarter=, [Order Date]=,DateNum={">=$(=Num(YearStart(max(DateNum)))))

<=$(=max(DateNum))"}>}Sales)

MTD -

sum({<Year=,Month=, Quarter=, [Order Date]=,DateNum={">=$(=Num(monthstartStart(max(DateNum)))))

<=$(=max(DateNum))"}>}Sales)

[nihhalmca]

helpful, thanks.

[ecolomer]

Excelent

Good Work. Thank's for sharing

Saludos,

Enrique Colomer

[qlikviewuser20]

Hi,

thank you for your post.

I did use the YTD function but it doesn't work for me.

I have a simple table with :

date	Amount
2015/12/31	43315257
2020/06/30	10087819
2020/07/31	-1269887020
2020/08/30	7597395

i did use this expression :

sum({<YEAR={$(=($(vCurrentYear)))}, DATE={"<=$(=Max(DATE))"}> }Amount)

to calculate the amount of the current month till the amount of january of the current year but it doesn't work

I also tried this expression:

Sum({< DATE={">=$(=Num(YearStart(Max(DATE))))<=$(=(Max(DATE)))"}>} Amount)

But same thing it just calculate the amount of the current month and i don't know how to fix that :s

Any help ?

Thank you very much !

[Anil_Babu_Samineni]

May be Remove Num in your second statement of first part