Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

# YTQ, QTD, MTD and WTD

cancel
Showing results for
Did you mean:
Partner - Champion

## YTQ, QTD, MTD and WTD

Last Update:

Mar 21, 2014 3:06:54 AM

Updated By:

CELAMBARASAN

Created date:

Mar 21, 2014 3:06:54 AM

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)

Regards,

Celambarasan

Labels (3)

• ### New to QlikView

Not applicable

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

Champion

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

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

Partner - Champion

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

Luminary Alumni

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

Not applicable

Hi Celambarasan,

can u post on previous ytd,mtd,qtd

Partner - Champion

Not applicable

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?

Partner - Champion

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.

Not applicable

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
Version history
Last update:
‎2014-03-21 03:06 AM
Updated by: