YTQ, QTD, MTD and WTD

[CELAMBARASAN]

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

[#2]

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

[datanibbler]

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

[CELAMBARASAN]

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

[richard_pearce6]

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

[#6]

Hi Celambarasan,

can u post on previous ytd,mtd,qtd

[CELAMBARASAN]

Hi qlikview11g

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

[#8]

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?

[CELAMBARASAN]

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

[#10]

Thank You So much it was simple and very helpful.

[#11]

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