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.
day(SaleDate) AS SaleDay,
month(SaleDate) AS SaleMonth,
year(SaleDate) AS SaleYear,
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).
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.