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
qlikviewuser20
New Contributor

Then i had a zero as value on my first row and still the same result for the other dates

0 Likes

May be create Thread in Community for output.

0 Likes
qlikviewuser20
New Contributor

Done!

YearToDate

0 Likes
momin_tahemas
Contributor

THANKS

It is very helpful

0 Likes
qlik4asif
Contributor II

It bypasses the selection for these fields,

0 Likes
frank_mcfarland
New Contributor

Hi everyone, thank you for your advises !! I tried to use the functions on Qlik Sense but I have some problem...

I have the field [DATE 1] that I load in this way:

Date(Date#(DATEREG, 'YYYYMMDD'), 'DD/MM/YYYY') AS [DATE 1]

I generated the autocalendar for [DATE 1]

I also load the field Floor(DATEREG) AS DateNum

Then I used the function for QTD that you advised:

Sum({<Year=, Month=, Quarter=, Week=, [DATE 1]=, DateNum={">=$(=Num(QuarterStart(Max(DateNum))))<=$(=Max(DateNum))"}>} SALES)

But it doesn't work properly, becouse it returns wrong values... What am I doing incorrectly?

0 Likes
aetingu12
Contributor

Thank you for this.  I was going a bit crazy trying to figure it out:)

0 Likes
vivian_kta
New Contributor III

As a beginner, I would like to understand

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

but not

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

My understanding is the expression will take the value of selected Date, thus result is the same. Why is MAX function  needed here? Any implication if I use the 2nd expression?

Thank you for explanation

0 Likes
boraste-sagar
New Contributor III

Current Year YTD

=Sum({<Date={">=$(YearStart(Today()))"}>}Marks)

Prior Year YTD

=Sum({<flag={'sd'},Date={">=$(=Floor(YearStart(AddYears(Today(),-1))))<=$(=Floor(YearEnd(AddYears(Today(),-1))))"}>}Marks)

Current Year QTD:

=Sum({<Date={">=$(=Floor(QuarterStart(Today())))"}>}Marks)

Prior Year QTD

=Sum({<flag={'sd'},Date={">=$(=Floor(QuarterStart(AddYears(Today(),-1))))<=$(=Floor(QuarterEnd(AddYears(Today(),-1))))"}>}Marks)

Current Year MTD

=Sum({<Date={">=$(=Floor(MonthStart(Today())))"}>}Marks)

Prior Year MTD

=Sum({<flag={'sd'},Date={">=$(=Floor(MonthStart(AddYears(Today(),-1))))<=$(=Floor(MonthEnd(AddYears(Today(),-1))))"}>}Marks)

Previous Quarter
=Sum({<flag={'od'},Date={">=$(=Floor(QuarterStart(Today(),-1)))<=$(=Floor(QuarterEnd(Today(),-1)))"}>}Marks)

Previous Month

=Sum({<Date={">=$(=Floor(MonthStart(Today(),-1)))<=$(=Floor(MonthEnd(Today(),-1)))"}>}Marks)

0 Likes
cmorri1988
Contributor

great post for beginners!

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-03-21 03:06 AM
Updated by: