

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Excellent Work Celambarasan. Hope this will helps to the beginners.......... Cheers


- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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)"


- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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
.png)
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Hi Celambarasan,
can u post on previous ytd,mtd,qtd


- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Hi qlikview11g
Please check this link Previous YTQ, QTD, MTD and WTD

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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?


- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Thank You So much it was simple and very helpful.

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
hi celambarasan,can you kindly look in to the scenario attached And provide solution.
Date | opcount | buying_count | sell_count | parts_date | calculation logic | comments | parts date | ||
1/1/2014 | 100 | 5 | 10 | 1-Jan-14 | opcount>cumulative count of sell | 100>=10 | condition is true and the date is min date i.e 1/1/2014 | ||
1/2/2014 | - | 10 | 50 | 1-Jan-14 | 100>=60 | condition is true and the date is unchanged | |||
1/3/2014 | - | 10 | 20 | 1-Jan-14 | 100>=80 | condition is true and the date is unchanged | |||
1/4/2014 | - | 10 | 20 | 1-Jan-14 | 100>=100 | condition is true and the date is unchanged | |||
1/5/2014 | - | 10 | 25 | 4-Jan-14 | 100>=125 | condition is false and the difference in stock is compared with the buying count in cumulative manner | |||
if cumulative buy>difference stock | condition 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 | - | 0 | 20 | 5-Jan-14 | buying count is zero | so 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 | - | 0 | 0 | 5-Jan-14 | the condition of stock does not change so the value remains unchanged as 5-jan-14 . | ||||
1/8/2014 | - | 0 | 0 | 5-Jan-14 | |||||
1/9/2014 | - | 0 | 0 | 5-Jan-14 | |||||
1/10/2014 | - | 0 | 0 | 5-Jan-14 | again buying count has values | ||||
1/11/2014 | - | 30 | 20 | 11-Jan-14 | Buying count>cumulative count of sell | here 30>=20 | The parts creation date is the date where again buying count has not null values. | ||
1/12/2014 | - | 10 | 10 | 11-Jan-14 | 30>=30 | condition is true and the date is unchanged | |||
1/13/2014 | - | 20 | 15 | 13-Jan-14 |