# QlikView Documents

Documents for QlikView related information.

# YTQ, QTD, MTD and WTD

MVP

## 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)

Regards,

Celambarasan

Labels (3)

• ### New to QlikView

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

MVP

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)

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

Honored Contributor

Good information. Thank you.

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)

Valued Contributor II

Honored Contributor II

Excelent

Good Work. Thank's for sharing

Saludos,

Enrique Colomer

New Contributor

Hi,

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 !

May be Remove Num in your second statement of first part

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