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

YTQ, QTD, MTD and WTD

cancel
Showing results for 
Search instead for 
Did you mean: 
CELAMBARASAN
Partner - Champion
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)

Will keep your expression simple

Regards,

Celambarasan

Comments
qlikviewuser20
Contributor III
Contributor III

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

0 Likes
Anil_Babu_Samineni

May be create Thread in Community for output.

0 Likes
qlikviewuser20
Contributor III
Contributor III

Done!

YearToDate

0 Likes
Anonymous
Not applicable

THANKS

It is very helpful

0 Likes
qlik4asif
Creator III
Creator III

It bypasses the selection for these fields,

0 Likes
Anonymous
Not applicable

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?

aetingu12
Creator
Creator

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

0 Likes
Anonymous
Not applicable

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

boraste-sagar
Contributor III
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
Creator
Creator

great post for beginners!

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