Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.

Very Useful YearToDate

Luminary
Luminary

Very Useful YearToDate

(Transcribed from http://qliktips.blogspot.com)

Many people will have used YearToDate (or Year2Date) to calculate out whether the current date is Year to date for this year, last year, etc.:

YearToDate(date_value)

or

YearToDate(date_value, -1)

But there are some other values of YearToDate that make it very useful. The full syntax is:

YearToDate( date [ , yearoffset [ , firstmonth [ , todaydate] ] ] )

The third parameter is very useful because it allows us to use the one function for MonthToDate and QuarterToDate to compare Year-on-Year or dynamically change between YTD, MTD and QTD (there are functions InMonthToDate and InQuarterToDate that could be used but they would require different Year offset calculations and are less easy to make dynamic use of)

For example:

YearToDate(date_value, 0, Month(Today()))

gives us Month to date this year.

YearToDate(date_value, -1, Month(Today()))

gives us Month to date last year.

With a handy use of the Ceil function, we can also calculate Quarter to date:

Ceil(Month(Today())/3) * 3 - 2

This value will always return the first month of the quarter containing the Month number passed (in this case, Month(Today()) )

So, plugging this into our YearToDate:

YearToDate(date_value, 0, Ceil(Month(Today())/3) * 3 - 2 )

gives us Quarter to date.

YearToDate(date_value, -1, Ceil(Month(Today())/3) * 3 - 2 )

gives us Quarter to date last year.

Use of appropriate variables means that we can give the user a very useful dynamic swap between YTD, QTD and MTD - if that is required.

Labels (1)
Comments
Not applicable

Hi, can you explain how can I do that with set analysis?

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2009-08-18 01:39 PM
Updated by: