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

Excellent Work Celambarasan. Hope this will helps to the beginners.......... Cheers

0 Likes
datanibbler
Esteemed Contributor

Very helpful indeed! Those are in fact among the most common expressions needed.

Just two little additions:

- Instead of explicitly stating the "independence from selections on the [Year_field]" etc., you could just use
   the parameter 1 (like {1< ... >} in your set_expression? - well, if you want to consider selections on other
   fields, that will not really make it easier, but if you want to disregard all selections for this, it will.

- in most instances, max(DateNum) will be "today" and QlikView has no data for dates after
   "today" (depending on your base_list, but usually that is the case) - no need to specify that "dateNum <=
   max(DateNum)"

0 Likes

Hi DataNibbler

My thoughts / comments on your additions

1. What if I'm going to look for the YTD of selective department by selections? Using {1<..>} won't let you do that. I feel its better to make the selection on the Time dimension ignored.

2. What if the user want to see previous year/month/quarter/week YTD/QTD/MTD/WTD?

Feel free to comment on my thoughts.

Regards,

Celambarasan

0 Likes
Luminary
Luminary

Thanks for the post. I've created a calendar script which help make set analysis for time periods very simple. It can be found here:

http://community.qlik.com/docs/DOC-6593

Regards

Richard

0 Likes
Not applicable

Hi Celambarasan,

can u post on previous ytd,mtd,qtd

0 Likes

Hi qlikview11g

Please check this link Previous YTQ, QTD, MTD and WTD

0 Likes
Not applicable

I have a questions about additional claculation added to YTD.

I'm trying to calculate YTD 2014 minus same period of last year (2013), which is no longer a YTD function in 2013. What is the best way to do this? Obviously I want to set it to variable, so I don't have to change anything going forward. Please advice?

0 Likes

Try

Current YTD - Previous YTD

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

In the place of Max(DateNum) use Today() if you want


Not applicable

Thank You So much it was simple and very helpful.

0 Likes
Not applicable

hi celambarasan,can you kindly  look in to the scenario attached And provide solution.

Dateopcountbuying_countsell_countparts_datecalculation logiccommentsparts date
1/1/20141005101-Jan-14opcount>cumulative count of  sell100>=10condition is true and the date is min date i.e 1/1/2014
1/2/2014-10501-Jan-14100>=60condition is true and the date is unchanged
1/3/2014-10201-Jan-14100>=80condition is true and the date is unchanged
1/4/2014-10201-Jan-14100>=100condition is true and the date is unchanged
1/5/2014-10254-Jan-14100>=125condition is false and the difference in stock is compared with  the  buying count in cumulative manner
if cumulative buy>difference stockcondition is true only on condition 35>25 i.e on 4-jan-2014.so that date is considered as parts creation date.
1/6/2014-0205-Jan-14buying count is zeroso the parts creation date is the date where last parts are procurred.i.e last date where buying quantity has values before it moved to null.Here 5-jan-14 is the parts creation date.
1/7/2014-005-Jan-14the condition of stock does not change so the value remains unchanged as 5-jan-14 .
1/8/2014-005-Jan-14
1/9/2014-005-Jan-14
1/10/2014-005-Jan-14again buying count has values
1/11/2014-302011-Jan-14Buying count>cumulative count of  sellhere 30>=20The parts creation date is the date where again buying count has not null values.
1/12/2014-101011-Jan-1430>=30condition is true and the date is unchanged
1/13/2014-201513-Jan-14
0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-03-21 03:06 AM
Updated by: