Tuesday 14 August 2012

Stephen Few Dashboard Design Competition

Stephen Few has opened his 2012 Perceptual Edge Dashboard Design Competition. Anyone can enter and you can find more details on his Perceptual Edge blog http://www.perceptualedge.com/blog/?p=1308

Considering what people say about QlikView lagging behind it's competitors in the looks department, I'd love to see a QlikView entry win!

Monday 13 August 2012

QlikViewAddict.com - 7000 Monthly Page Views

Astonishingly, QlikViewAddict.com is now getting over 7000 page views a month.

For a change, I'm truly speechless! So all I'll say is a huge thanks to all of you who take the time to read my ramblings and I'll try and post a little more often.

Thursday 9 August 2012

Dates in QlikView - Part 2

In Part 1 of this series about dates in QlikView, we looked at how dates are stored in QlikView and how we can manipulate and perform some basic conversion on them. Those principals are critical to working with dates in QlikView and I'd strongly recommend you read it before continuing with this part.

A common problem occurs with dates in QlikView when an underlying system or database stores dates as strings or even as separate day, month and year fields rather than true dates. For example, if the dates are stored as strings, subtracting one from the other won't give you the number of days between the two. So ideally we need to convert the badly stored dates to true dates when we load them into QlikView, and luckily there are easy methods to solve this problem using a group of functions known as "interpretation functions".

Let's look first at how to create a true date from a string. Luckily, QlikView has a simple interpretation function that solves this problem for us. The date#() function interprets a string using the given format and creates a true date from it. Take special note of the #, its not a typo and differentiates it from the formatting function date() which we looked at in Part 1. Its straightforward to use as follows:

date#(DateStringField, 'YYYY-MM-DD') AS DateField

The function interprets the field DateStringField using the format YYYY-MM-DD and if it can do so, outputs a true date. Remember from Part 1 that QlikView stores dates as a dual value; the numerical representation of the date and a string representing how the date will be presented to the user. One thing worth noting about the date#() function is that the resulting date will be formatted using the same format used to interpret the string, which might not be the format you desire your dates to be displayed in. You should also remember that we can use the date() function to format the string part of a true date and so we can add it to the above code as follows:

date(date#(DateStringField, 'YYYY-MM-DD'), 'DD-MM-YYYY') AS DateField

Working with timestamps stored as strings is made just as simple in QlikView using the timestamp#() function instead. It works in exactly the same way and can be used like this:

timestamp#(TimestampStringField, 'YYYY-MM-DD hh:mm:ss') AS TimestampField

Again, you can wrap it in the timestamp() function if you need to format the resulting timestamp differently.

Great, we've solved the first problem, or have we? What if the field contains dates stored as strings of more than one format. For example, I may have dates stored as a mixture of formats such as YYYY-MM-DD, DD-MM-YYYY and DD/MM/YYYY. As with lots of things in QlikView, there is more than one solution but the simplest is a very underused QlikView function. The alt() function returns the first value passed to it that is numerical, and because a true date in QlikView is a dual value (part of which is a number) we can use it to return the first date#() format that manages to create a true date.

alt(date#(DateString, 'YYYY-MM-DD'), date#(DateString, 'DD-MM-YYYY'), date#(DateString, 'DD/MM/YYYY'), 'No Date Found') AS Date

If the first date#() function manages to interpret the string as a true date, then it will be returned, if not then it will move on to the next, and so on. If no numerical value is found, the alt() function will return the last value in the parameter list and so in this example, if it can't interpret the string using any of the given formats then it will return the string "No Date Found".

OK, so now we've resolved dates stored as strings, but what about our other problem of dates split between separate day, month and year fields. You might not see this as a problem at all, we often split up a date in QlikView into the day, month and year to make it simpler to use in charts, but it is vital to also have it at as true date if you want to be able to perform calculations with it. This is thankfully a really easy problem to solve using the makedate() function. It accepts up to 3 parameters, the year, the month (if omitted then Jan is assumed) and the day (if omitted the 1st is assumed) and creates a true date from them.

makedate(YearField, MonthField, DayField) AS Date

And of course there is also a maketime() function which can be combined with the makedate() function as follows to create a timestamp:

makedate(YearField, MonthField, DayField) + maketime(HourField, MinuteField, SecondField) AS Date

Remember that dates are integer (whole number) values for the number of days past the QlikView epoch date and time is a fraction of a day, and so we can simply add the two values together to create the timestamp. However, note that because we have used the + operator, QlikView assumes that you are only interested in a numerical answer and so we need to wrap the entire thing in the timestamp() function to format it correctly as a dual value like this:

timestamp(makedate(YearField, MonthField, DayField) + maketime(HourField, MinuteField, SecondField)) AS Date

Hopefully from this you can see that its relatively simple to create a true date from just about any way a date can be store in a database or other underlying system.

In the next part we'll look at how to perform more manipulations and complex calculations using dates, such a splitting up dates into separate fields, working out if a date is within the month to date, year to date, last year to date etc.