Qlik Community

QlikView Documents

Documents for QlikView related information.

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
satishkurra
Valued Contributor II

Hi Celambarasan

This is an excellent post. Really helped me.

1 minor correction in the above expression.

Instead of AddYear it has to be AddYears

Thanks

Satish

0 Likes
Partner
Partner

Thanks Celambarasan.

Topics like these are generally confusing and you made it very clear.

Cheers,

Naresh

0 Likes
Not applicable

Hi Celambarasan,


Able to calculate the YTD from your expression but the MTD is not working. Using the following expression:


=Sum({<Month={$(=max(Month))}, Date={'>=$(=Num(MonthStart(Max(Date))))<=$(=Max(Date))'}>} [Sales])



0 Likes

Is your date field is in number format if not try the below


=Sum({<Month={$(=max(Month))}, Date={'>=$(=MonthStart(Max(Date)))<=$(=Date(Max(Date)))'}>} [Sales])


Not applicable

Hi Celambarasan,


thanks for the help..


Also i am trying to find the previous year YTD sales, using the following expressions to calculate:


1. =Sum({$<Year={$(=max(Year-1))}, Date={'>=$(=YearStart(Max(Date-365))))<=$(=Date(Max(Date-365)))'}>}[Sales]). This one is working.


2. =Sum({$<Year={$(=max(Year-1))}, Date={'>=$(=YearStart(MakeDate(max(Year-1),Month, Day(Date))))))<=$(=MakeDate(max(Year-1),Month,Day(Date))))'}>}[Sales]). This one is not wokring.


Can you please help on the second expression what may be the error in it.



0 Likes
yakir_manor
New Contributor III

i resolved it in a nicer way, i wanted to add a dimension called period so i can select or deselect it, that way i can use it in bookmarks, you can see my implementation, i wanted:

7 last days - 1w.

last month - 1m - 30 days.

last year - 365 days back.

you can see my solution in the attached file, you can add more periods or change the calender the same way you can see my comment with attachment in

Qlikview Financial periods (MTD, YTD, LMTD, LYYTD)

enjoy!

0 Likes
Karim_Khan
Contributor III

Hi Celambarasan,

   By applying your Login am getting the calculation error.

QTD

=Sum({<Quarter={'$(vQuarter)'}, Quarter={'$(vQuarter)'}, Month={'$(vMaxMonth)'}, Week={'$(vWeek)'}, Date={'$(vMaxDate)'}, DateNum={'>=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))'}>} [Net Bookings]).

Its is showing me the same calculation as of Monthly basis.

Regards,

KK

0 Likes
Not applicable

Hi, Im new to Qlik, coming from Microstrategy , where I have to implement the YTD function ? I dont know where I have to put the function:

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

I assume that "sales" is referencing the sales table (in my case it has another name)


thanks!

0 Likes
satishkurra
Valued Contributor II

‌hi

you need to use the above expression while creating chart

see if the below helps

after reloading the data in Qlikview , right click on the ui Layer. Create chart and select chart type , click next and specify your dimension and click next , you will get an edit expression window. We need to enter the expression here.

hope it helps...

thanks

satish

0 Likes
Not applicable

Ok, thanks a lot for your response, it works, Im starting to understand the way qlikview works, I dont know if I can achieve the approach that I would like , I will try to explain: for example I want to create the same expression, but instead for one graphic for the entire document, so I would reuse it across other objects with other attributes, like YTD sales by month, YTD sales by city, etc.

Dont know if that is possible, is like declaring a metric like in microstrategy, or in Microsoft PowerBI (DAX)

thanks!!

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