Qlik Community

QlikView Documents

Documents for QlikView related information.

YTQ, QTD, MTD and WTD

YTQ, QTD, MTD and WTD

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

Comments
Not applicable

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...

0 Likes

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)

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

0 Likes

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

0 Likes
ahaahaaha
Honored Contributor

Good information. Thank you.

0 Likes
vireshkolagimat
Contributor III

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
Valued Contributor II

helpful, thanks.

0 Likes
ecolomer
Honored Contributor II

Excelent

Good Work. Thank's for sharing

Saludos,

Enrique Colomer

0 Likes
qlikviewuser20
New Contributor

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 :

dateAmount
2015/12/3143315257
2020/06/3010087819
2020/07/31-1269887020
2020/08/307597395

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 !

0 Likes

May be Remove Num in your second statement of first part

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-03-21 03:06 AM
Updated by: