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
vvvvvvizard
Contributor III

On Sat, 29 Sep 2018, 10:09 PM Sagar Boraste, <qcwebmaster@qlikview.com>

0 Likes
dorianqlik
New Contributor

Hello,

Ive tried to single out your expression for Previous YTD but im only getting the same value as YTD expression.

My mission: I want to have two colums with one with YTD vale and next YTD -1 year

Regards

0 Likes
syedmazhar
New Contributor

What should be the MDT,YDT and QTD expression if my financial year between April to March?

0 Likes
FlAsh92
New Contributor III

I tried using the same formula as yours, but the value of the YTD column is always equal to Sum(Sales).

For eg: If I select the date as 5/2/2019, then the YTD and Sum(Sales) cannot be equal (Sales has happened on 1st, 2nd, 3rd and 4th of Feb too).

Can anyone help me out with the reason for this, please?!

 

@celambarasan 

0 Likes
khanashique
Contributor II

Hi Celam,

Is there any easiest way to handle the above expressions with out master calendar...

 

Thanks.

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