Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More

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: