Sunday, 17 March 2013

Dates in QlikView - Part 3

It's taken me a little longer than I wanted to complete this next instalment of my series of posts on dates in QlikView. I wanted to get the post about the dual() function finished first as it can be very useful when working with dates and is referenced below. My work commitments keep getting in the way too. Anyway, let's crack on...

In the first 2 parts of this series, I looked at how dates are stored in QlikView and how to ensure you have them in the right format to be most effective. So now that we have that out the way, lets looks at how we can start to make dates more useful for using in charts and objects within the front end of QlikView.

Despite looking in part 2 at how we worked to combine parts of a date stored separately in order to create a "true date" from them, we often want to do the reverse also and split the date into its composite parts. For example, we might want to be able to select individual years without having to select every date in those years, or we might wish to be able to display a bar chart showing our sales by month.



To do this we need to separate out the months and years from the dates and thankfully, as we've seen before, this is made very simple to do in QlikView using the inbuilt functions. The most common of there are the day(), month() and year() functions.

Sales:
LOAD
    SaleID,
    SaleDate,
    day(SaleDate) AS SaleDay,
    month(SaleDate) AS SaleMonth,
    year(SaleDate) AS SaleYear,
    SalesPersonID
FROM Sales.qvd (qvd);


Most of you will have seen these functions before and they do exactly what you might expect, returning the individual parts of the date they are passed. One thing worth noting is that the month() function will return a useful dual value, containing both the numerical value for the month and also the month name (as defined in the QlikView environment variables). This means when using them in a chart, we can sort them numerically to get the months in the correct order.

There are some additional functions that can also be used to break out parts of a date that might not be as obvious. For example:
  • The week() function returns a number representing the week of the year.
  • The weekday() function returns a dual value defining an integer representing the day of the week (Monday being 0, Tuesday being 1, etc) and a string representing the week day name (as defined in the system variables). 
  • The dayNumberOfYear() function returns an integer defining the day number within the year, starting at 1 for the 1st of January and continuing to 365 31st of December (Or 366 for a leap year). 
One thing that is often needed but I've left out of this list is a function to obtain the quarters of the year. The quarterName() function can be used but it's formatting is fixed (for example "Oct-Dec 2012") and so it isn't always ideal if I need my quarters named differently. I personally prefer to use a different approach using some simple maths instead as follows:

ceil(month(DateField)/3) AS DateFieldQuarter

This short piece of code results in an integer between 1 and 4 representing the quarters of the year. Once you have calculated your quarter numbers, you can then combine them with any strings necessary to format as you desire. Better still would be to use the dual() function to create a true dual value for the quarter like this:

dual('Quarter ' & ceil(month(DateField)/3), ceil(month(DateField)/3)) AS DateFieldQuarter

I won't go in to detail about what the dual function does here as it has many uses but you can read all about it in my previous post The QlikView dual() Function


Another common problem that seems to challenge those new to QlikView is how to analyse months across more than one calendar year. For example, I wish to see a line chart showing the trend of my sales between August 2011 and April 2012 and should look something like this:


You might try combining the year() and month() functions like this:

month(DateField) & ' ' & year(DateField) AS DateFieldMonthYear

This would give you a field that looks correct on the surface but using it becomes a problem. Because the result of above code is a string, getting it to sort into the correct order in the chart is somewhat difficult. All the Aprils will appear first, then all the Augusts, etc. It is possible to get it to sort correctly but there is a better solution that will sort automatically. The next method people tend to try is to use the date() function like this:

date(DateField, 'MMM YYYY') AS DateFieldMonthYear

Again on the surface it may appear this method gives the result we require but under the surface there is a critical underlying issue. As we looked at in part 1 of this series of posts on dates, QlikView stores data as dual values where possible and because of this, dates are stored in 2 parts, an underlying number representing the number of days offset from the epoch date, and a string representation of how the date should be presented. With this in mind, the date() function is a formatting function which acts only on the string part of the dual value and because of this, the underlying number is unaffected. This means that QlikView will still see 01/01/2013 formatted as "Jan 2013" as different to 02/01/2013 formatted in the same way "Jan 2013". The solution is very simple:

date(monthstart(DateField), 'MMM YYYY') AS DateFieldMonthYear

The monthstart() function does what it says on the proverbial tin, it returns a true date value which is the first day of the month for the passed date. We then use the date function as above to format the displayed string as we require.

OK, so we've now split our date field in to many different parts but all have been based on the assumption that a year starts on the 1st of January. What if it doesn't? Nope I've not gone mad, I am referring to the all too common fiscal years (and as a result quarters) that start part way through a calendar year. Let's take the most common fiscal year as our example, beginning on the 1st of April instead. As I've said many times on this blog, there is more than one way to solve this problem but the one below is my favourite because it is simple and easily adapted to handle different financial periods.

The first step is to define a variable containing the number of the calendar month that the financial year starts on. So for our example the first month in our fiscal year is April and so we define the variable like so:

SET vFirstMonth = 4;

We are going to use this variable and some simple math to calculate our fiscal periods. However we are missing one important thing, an ability to accurately subtract months. Keeping in mind what was discussed in part 1 of this series about how dates are stored, you might assume we can simple subtract the relevant number of days. But how many days are in 3 months (the number of months prior to our vFirstMonth variable)? Because the number of days in a calendar month varies between 28 and 31, we need a more accurate way than simply subtracting a number of days. Thankfully QlikView has a function to do the job, but its name could cause some confusion. The addmonths() function can be used to both add and subtract a given number of months from a date. So combining the addmonths() function, the dual() function, what we've covered about dates already and some simple math we can create our fiscal periods like this:

dual(year(addmonths(DateField, 1-$(vFirstMonth))) & '/' & right(year(addmonths(DateField, 1-$(vFirstMonth)))+1,2), year(addmonths(DateField, 1-$(vFirstMonth)))) AS DateFieldFiscalYear,
dual('Quarter ' & ceil(month(addmonths(DateField, 1-$(vFirstMonth)))/3), ceil(month(addmonths(DateField, 1-$(vFirstMonth)))/3)) AS DateFieldFiscalQuarter,
num(month(addmonths(DateField, 1-$(vFirstMonth))))AS DateFieldFiscalMonth


The addmonths() function turns the first date of our fiscal year 1st April into the first date of the calendar year 1st January. We then use the year() function and some string formatting to create the fiscal year and the same code we used above to calculate the calendar quarters to work out the fiscal quarters. The dual() function is used for both to ensure they not only display as we wish but they also sort correctly too. The DateFieldFiscalYear  field will be displayed in the format "2013/14" and have an underlying number of 2013. The DateFieldFiscalQuarter field will be displayed in the same format as our DateFieldQuarter field above "Quarter 1" with an underlying numeric value of 1. The DateFieldFiscalMonth field returns the number of the month in the financial year (April being 1, May being 2, etc).

A quick word of warning, you may be tempted to perform this kind of manipulation of dates and times in calculated list boxes or dimensions rather than creating additional fields within the data model. This is considered bad practice however and should be avoided if possible as it slows down the user interface. Keep the manipulation within the script for maximum application performance.

In the next installment of this series we'll take a look at how to work out year-to-date and month-to-date calculations using both flags in the data model and set analysis. We'll also look at how to work out not only the number of days between 2 dates (covered in part 1 of this series) but the number of weeks, months and year.

31 comments:

  1. Thanks for the post. Very informative and helpful!

    ReplyDelete
    Replies
    1. You're welcome, good to know it's of use!

      Delete
    2. How can i start quarter from Saturday? so first Saturday of April (2 Aprl 2016) would be FY17 Q1?

      Delete
  2. Why not simply use MonthName?

    ReplyDelete
  3. Hi Tim

    The monthname() function is perfectly valid but it is limited in that you can't change the format of the output. It is fixed to "MMM YYYY". Using date() and monthstart() functions allows you to change the output format if needed like this:

    date(monthstart(DateField), 'YYYY-MMM')

    Regards
    Matt

    ReplyDelete
  4. Very nice...I liked the way you explained this topic!!!

    ReplyDelete
    Replies
    1. Thanks, glad you enjoyed it!

      Regards
      Matt

      Delete
  5. Nicely explained all the 3 series. Thanks

    Any idea when the 4th Date part series be released?

    ReplyDelete
  6. Matthew, I'm quite new to Qlikview as well and was wondering why Time Chart Wizard keeps complaining that the base date expression is bad. I've tried a couple of different things to set the base date to 2008-06-01 in this case. I'm guessing it has something to do with me not converting the resulting expression to a number (since it is stored as a dual value). For instance, makedate(2008,6,1) doesn't work. Could you help me out please?

    ReplyDelete
  7. how to show tooltip on table?? am using sparklin in table. please suggest me how to show tooltip on sparklin for various dimensions

    ReplyDelete
  8. Hi Matthew - when I modify this further to calculate the Fiscal Week using

    num(week(AddMonths(appointmentDateTime, 1-$(vFirstMonth))))AS DateFieldFiscalWeek

    I get an anomaly whereas the week ranges are a day out (ie Tuesday to Monday the following week, not Monday to Friday) .... any assistance greatly appreciated!

    ReplyDelete
  9. How to get the latest end of month for a given month or date??

    ReplyDelete
  10. Hi Matt
    I have gone through all the 3 series on Date and let me tell you it helped me a lot to clear my concept on Date. But there are still some areas where i get stuck for example if i need to find the Date of the Last Thursday of the Current month. Say in the month of June,2014 the Last Thursday is 26th,
    I tried using a combination of DayName and MonthEnd but didn't got the desired output. Would appreciate if you can cover this in your next series.

    ReplyDelete
    Replies
    1. Hi
      I'm glad you found the posts useful. Dates seem to be a topic many people have problems with. As or your "last Thursday" problem, after a little digging I've found I've done something similar before, try this code:

      if(num(weekday(monthend(F1)))<=3,
      date(monthend(F1)+3-num(weekday(monthend(F1))))
      ,
      date(monthend(F1)-num(weekday(monthend(F1)))+3)
      )

      It will work for whatever day of the week you want, simply change the 3's to the relevant day of the week (monday=0, tuesday=1, etc). F1 is obviously the field containing the date you want to find the last Thursday for.

      Regards
      Matt

      Delete
    2. Thanks a Lot Matt.

      Cheers :)

      Delete
  11. I verified your above statement,it works fine for all the months except the Months which starts from Sunday.For example June,2014,01/06/2014 was Sunday ideally the expression should return 26/06/2014 as Last Thursday for the Month of June but it is returning 03/07/2014. Similarly in 2013,1st September was Sunday and for the month end 30/07/2013,i should usually get 26/09/2013 as it was the Last Thursday for that month.

    If i simply replace +3 with -4 in your If statement then it gives me the correct output for the months which starts on Sunday.

    Do let me know if you have any other generic solution which will fit for all the months irrespective of on which day the month starts.

    Thanks once again.

    ReplyDelete
  12. HI Matt
    Finally i was able to resolve the problem and this is what i did,i added an if statement just to check whether the Month Start date is Sunday or not if its Sunday then date(monthend(DateField)-num(weekday(monthend(DateField))) -4) else date(monthend(DateField)-num(weekday(monthend(DateField)))+3).

    if(Weekday(MonthStart('30.06.2014'))='So',date(MonthEnd('30.06.2014')-num(weekday(MonthEnd('30.06.2014')))-4),date(MonthEnd('30.06.2014')-num(weekday(MonthEnd('30.06.2014')))+3))

    But let me be honest here i was able to resolve the problem becoz it was you who provided that invaluable tip.

    Regards
    Vineet

    ReplyDelete
    Replies
    1. Glad you found the solution!
      Regards
      Matt

      Delete
  13. Hi Matt, is part 4 hidden away anywhere? I find your examples and explanations very useful and am just trying to use year to date for the first time and hoped to see the next post.

    thanks

    John

    ReplyDelete
    Replies
    1. Hi John
      Glad you find the blog useful. Part 4 is almost complete and I'm hoping to get it finished soon.

      Regards
      Matt

      Delete
  14. Thanks Mathew,

    I have been looking for this solution for a long time.
    Amazing job.

    Cheers,
    Chris

    ReplyDelete
    Replies
    1. Hi Chris
      I'm glad you found it useful. I was hoping to get part 4 out by now but I've been stricken with something resembling the plague and so haven't been able to finish it off. I'll try get it out soon as possible.

      Regards
      Matt

      Delete
    2. Hi Mathew,

      I am looking forward to Part 4. Thanks for the amazing job done. It easily saved 3-4 days for me.

      Regards,
      Chris

      Delete
  15. Hi Matthew,

    I just started to build reports in Qlikview and seek some help for the following.

    I encountered a small issue while trying to do Year on Year analysis in Qlikview.
    I want to show column for June in the previous financial year (i.e June 2014 for 2013/2014 fin year).

    I tried to use standard approach which is
    =Sum ({$} Amount)).

    However, QlikViewdoes not seem to recognize my FY field as numerical. It works with max (FY) but not with max(FY)-1.

    I do not have a master calendar. Here are all my date-related fields in the load script.

    Month(Date) as Month,
    Year(Date) as Year,
    quarterName(Date) as Quarter,
    Date(Monthstart(Date), 'MMM-YYYY') as YearMonth,
    Date(yearstart(Date,0,7),'YYYY') as FY,

    dual(year(addmonths(Date, 1-$(vFirstMonth))) & '/' & right(year(addmonths(Date, 1-$(vFirstMonth)))+1,2), year(addmonths(Date, 1-$(vFirstMonth)))) AS DateFieldFiscalYear,

    dual('Quarter ' & ceil(month(addmonths(Date, 1-$(vFirstMonth)))/3), ceil(month(addmonths(Date, 1-$(vFirstMonth)))/3)) AS DateFieldFiscalQuarter,

    num(month(addmonths(Date, 1-$(vFirstMonth))))AS DateFieldFiscalMonth,

    Thanks a lot in advance,
    Chris

    ReplyDelete
    Replies
    1. Here is my full post in QlikView Community, some fields are not showing correctly here.

      http://community.qlik.com/message/676503

      Thanks!

      Delete
  16. How Can we show All Months in a single row like
    Aug
    Sep
    Oct
    Like this format how can i get ?

    ReplyDelete
  17. Hi Matt
    Just to say thanks for all your posts on Dates in Qlikview they have been most helpful. Without being smart in any way shape or form, you do have a lovely typo in one section which is " As we looked at in part 1 of this SERIOUS of posts on dates". I like this typo only because it works so well when you work with dates because they cause all sorts of SERIOUS problems.

    Cheers and thanks for all the help

    ReplyDelete
    Replies
    1. Hi John
      Glad you found the posts useful! Going by the traffic this site gets then it would appear you are not alone in having had problems with date. Joins and nulls seem to be equally as troublesome.

      Good spot on the typo! At least it made you smile.

      Regards
      Matt

      Delete