This is a good function to have around and one that is useful to demonstrate being able to have optional parameters in functions. We just need to check that a parameter is not nil. If it is, we can set a default value.
Here is the code:
function inyeartodate(vdate, vbasedate, vshift)
-- Accept a date and compare it to the base date
-- If it is in the same year up to and including the
-- basedate, then true. The shift changes the
-- year to compare. E.g. -1 is YTD last year.
rval = 0
-- The parameters may not have been passed so establish defaults
if vdate == nil or not is.datetime(vdate) then vdate = datetime.timestamp() end
if vbasedate == nil or not is.datetime(vbasedate) then vbasedate = datetime.timestamp() end
if vshift == nil or not is.number(vshift) then vshift = 0 end
-- In case the shift is not an integer, floor it
if not is.integer(vshift) then vshift = math.floor(vshift) end
-- If the vshift is not zero, we ajust the base date
if vshift ~= 0 then vbasedate = datetime.adjust(vbasedate, vshift, "y", true) end
-- If the vdate is less than the base date,
-- and they are in the same year, then
-- the result is true - return -1
if vdate <= vbasedate and datetime.moment(vdate, "y") == datetime.moment(vbasedate, "y") then
rval = -1
end
return rval
end
And you might call it like:
d = string.datetime("20120514", "CCYYMMDD")
b = string.datetime("20130531", "CCYYMMDD")
print (inyeartodate(d))
print (inyeartodate(d, b, -1))
Another interesting thing to note is the "true" parameter to the datetime.adjust function. This causes the function to use 365 days in the calculation rather than 365.25.
Stephen Redmond is CTO of CapricornVentis a
QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
Monday, 13 May 2013
Month function in Lua for QlikView Expressor
Lua doesn't have a Dual data type like QlikView's. However, it does have a native Table data type that could allow us to simulate a Dual. At the end of the day, you are still going to have to the different values as separate outputs, but it could be useful to have a simple function that will give you one or the other.
Here you go:
function month(vdate)
rval = nil
-- Set up a table of Month names
local months = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}
if is.datetime(vdate) then
-- Get the month number using datetime.moment
local monthnum = datetime.moment(vdate, "m")
-- Return a table of the Text and Num values
rval = {Text = months[monthnum], Num = monthnum}
end
return rval
end
This would be called something like this:
d = string.datetime("20130513", "CCYYMMDD")
print (month(d).Text, month(d).Num)
Enjoy.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
Here you go:
function month(vdate)
rval = nil
-- Set up a table of Month names
local months = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}
if is.datetime(vdate) then
-- Get the month number using datetime.moment
local monthnum = datetime.moment(vdate, "m")
-- Return a table of the Text and Num values
rval = {Text = months[monthnum], Num = monthnum}
end
return rval
end
d = string.datetime("20130513", "CCYYMMDD")
print (month(d).Text, month(d).Num)
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
AddMonths function in QlikView Expressor
QlikView Expressor comes with a programming language, called Lua, to which they have added their own objects and functions. One of these is the datetime object which has several functions to allow the manipulation and parsing of dates - something that will be done in an ETL environment quite frequently.
One of these function is adjust - used to add or subtract different time periods from a date. When looking at the documentation, there was an interesting, to me anyway, omission in the type of period that you can adjust a date by. It does include seconds, minutes, hours, days, years and centuries, but no months!
Now, month arithmetic should be straightforward but there are complications when you have dates after the 28th of any month - what happens if you subtract 3 months from the 29th May 2013? If you don't handle it correctly, you will get an invalid date.
In this Lua function, I handle that eventuality by using a Lua pcall function to test if the returned date is valid and, if not, subtracting a day until it is.
function AddMonths(dt, months)
-- Parse the month, year and day of the date
mn = datetime.moment(dt, "m")
yr = datetime.moment(dt, "y")
dy = datetime.moment(dt, "d")
-- Add the number of months to the month
mn = mn + months
-- If this resulted in a month value over 12,
-- then we increment the number of years and
-- decrease the month value by 12 until it is
-- less than 12 again
while mn > 12 do
mn = mn - 12
yr = yr + 1
end
-- Similarly, if the calculation leaves us
-- less than zero, we need to subtract years
while mn < 1 do
mn = mn + 12
yr = yr - 1
end
-- If the day value is over 28, the convert might not parse so we drop the number
-- of days until it does (e.g. starting with 31st May, subtract 3 months gives
-- Feb - 31st Feb is an error so we need to go back to 28th Feb)
if dy > 28 then
-- Wrap the call in a pcall() to test if it works - if it fails, we subtract a day
while not pcall(function() string.datetime(mn .. "/" .. dy .. "/" .. yr, 'M*/D*/YYYY') end) do
dy = dy -1
end
rval = string.datetime(mn .. "/" .. dy .. "/" .. yr, "M*/D*/YYYY")
else
rval = string.datetime(mn .. "/" .. dy .. "/" .. yr, "M*/D*/YYYY")
end
return (rval)
end
So, you would call it something like this:
bd = string.datetime("20130529", "YYYYMMDD")
print(datetime.string(AddMonths(bd, -3), "CCYY-MM-DD"))
This may not be be most efficient way to do this, but it is a useful demonstration of using pcall and doing month arithmetic.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
One of these function is adjust - used to add or subtract different time periods from a date. When looking at the documentation, there was an interesting, to me anyway, omission in the type of period that you can adjust a date by. It does include seconds, minutes, hours, days, years and centuries, but no months!
Now, month arithmetic should be straightforward but there are complications when you have dates after the 28th of any month - what happens if you subtract 3 months from the 29th May 2013? If you don't handle it correctly, you will get an invalid date.
In this Lua function, I handle that eventuality by using a Lua pcall function to test if the returned date is valid and, if not, subtracting a day until it is.
function AddMonths(dt, months)
-- Parse the month, year and day of the date
mn = datetime.moment(dt, "m")
yr = datetime.moment(dt, "y")
dy = datetime.moment(dt, "d")
-- Add the number of months to the month
mn = mn + months
-- If this resulted in a month value over 12,
-- then we increment the number of years and
-- decrease the month value by 12 until it is
-- less than 12 again
while mn > 12 do
mn = mn - 12
yr = yr + 1
end
-- Similarly, if the calculation leaves us
-- less than zero, we need to subtract years
while mn < 1 do
mn = mn + 12
yr = yr - 1
end
-- If the day value is over 28, the convert might not parse so we drop the number
-- of days until it does (e.g. starting with 31st May, subtract 3 months gives
-- Feb - 31st Feb is an error so we need to go back to 28th Feb)
if dy > 28 then
-- Wrap the call in a pcall() to test if it works - if it fails, we subtract a day
while not pcall(function() string.datetime(mn .. "/" .. dy .. "/" .. yr, 'M*/D*/YYYY') end) do
dy = dy -1
end
rval = string.datetime(mn .. "/" .. dy .. "/" .. yr, "M*/D*/YYYY")
else
rval = string.datetime(mn .. "/" .. dy .. "/" .. yr, "M*/D*/YYYY")
end
return (rval)
end
bd = string.datetime("20130529", "YYYYMMDD")
print(datetime.string(AddMonths(bd, -3), "CCYY-MM-DD"))
This may not be be most efficient way to do this, but it is a useful demonstration of using pcall and doing month arithmetic.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
Wednesday, 1 May 2013
Bricks on maps instead of circles
In his recent newsletter, Stephen Few introduces the concept of using bricks as "A New, More Perceptible Method for Encoding Quantitative Values in Geospatial Displays".
Now, I am not a fan of geospatial data visualizations at the best of times and have blogged and tweeted about that in the past (for example, see Do we really need maps for analysis?) I particularly don't like the ideal of using bubble size to encode value on a map so the idea the bricks might be a better idea was intriguing.
Stephen uses a map of the US and juxtaposes the use of bricks with another map using circles to contrast. It is quickly obvious that the bricks are much easier to compare, say, Washington State with Florida. With the circles it is difficult to perceive the difference. With the bricks, one can see the difference easily.
Inspired by Stephen, and challenged by Brian Dunphy on Twitter, I decided to have a go at doing this in QlikView. It was a fair challenge and helped me build up some skills with using the jQuery library (which is embedded with QlikView in version 11+ so you don't need to load it separately). Here is the result (using the old QlikView Google Maps demo document of London properties):
Not bad! But I quickly noticed a problem with this application, which Stephen mentions in his article. When circles overlap, they are still legible. When the bricks overlap, they become completely unreadable!
So, it can work quite well where the data points are spread apart, but not great where there is overlap. I think that if you are doing something for states / provinces / countries, where the data points are more distinct and can be controlled, then this can be useful. But I might still choose a bar chart!
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
Now, I am not a fan of geospatial data visualizations at the best of times and have blogged and tweeted about that in the past (for example, see Do we really need maps for analysis?) I particularly don't like the ideal of using bubble size to encode value on a map so the idea the bricks might be a better idea was intriguing.
Stephen uses a map of the US and juxtaposes the use of bricks with another map using circles to contrast. It is quickly obvious that the bricks are much easier to compare, say, Washington State with Florida. With the circles it is difficult to perceive the difference. With the bricks, one can see the difference easily.
Inspired by Stephen, and challenged by Brian Dunphy on Twitter, I decided to have a go at doing this in QlikView. It was a fair challenge and helped me build up some skills with using the jQuery library (which is embedded with QlikView in version 11+ so you don't need to load it separately). Here is the result (using the old QlikView Google Maps demo document of London properties):
Not bad! But I quickly noticed a problem with this application, which Stephen mentions in his article. When circles overlap, they are still legible. When the bricks overlap, they become completely unreadable!
So, it can work quite well where the data points are spread apart, but not great where there is overlap. I think that if you are doing something for states / provinces / countries, where the data points are more distinct and can be controlled, then this can be useful. But I might still choose a bar chart!
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
Tuesday, 23 April 2013
Performance using Labels or Column() in charts
If I have a chart that calculates Total Sales: Sum(Sales), and Total Costs: Sum(CostPrice*Quantity), then I have 3 options to calculate the Total Margin:
Option 1: I can just use the same expressions in full:
Option 2: I can reference the column values using the Column() function:
Option 3: I can reference the labels of the first expressions:
Now, someone once said to me that the last option, although it might appear to be the best because you are re-using existing values, actually calculates slower than the others. I have often wondered if that was true. Someone else suggested to me recently that the 2nd option actually performs quicker. I decided that I should find out!
Buried deep in the Document Properties of a QVW, on the Sheets tab, there is a list of all the objects on the sheet along with their last calculation time (CalcTime) in milliseconds. If you looked at this for one of your charts, you might find that it says "0". This is because the last calculation might have been from cached data so the only accurate CalcTime is when the chart is first initialized in QlikView or when the first of a particular set of selections are made in a recently opened document - where the cache has not been established yet.
So, what I did was to take a document with about 5,000,000 sales transaction rows and build a simple Straight Table of Sales / Costs / Margin by Country. I made 3 copies of this document, each copy making the Margin calculation in one of the above ways. The only object in each document is the straight table.
I then completely closed QlikView. I opened one of the documents, recorded the "Initialize" CalcTime for the chart, made a selection of 2 countries (UK and USA) and recorded the "Selection" CalcTime for the chart. Then I shut down QlikView again and repeated. I did this 8 times for each of the documents, opening them in random order.
Here are the results:
There is some slight differences. But, statistically, I would have to say that there is no real difference between the 3 options!
One thing that I would say is that I don't like using the Column() function in a Straight Table. This is because the user may have the option to swap columns around and that would invalidate the expression. Other than that, it appears that you can just use whatever option you like and it won't make any difference to QlikView performance.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
Option 1: I can just use the same expressions in full:
Sum(Sales) - Sum(CostPrice*Quantity)
Option 2: I can reference the column values using the Column() function:
Column(1)-Column(2)
Option 3: I can reference the labels of the first expressions:
[Total Sales] - [Total Costs]
Now, someone once said to me that the last option, although it might appear to be the best because you are re-using existing values, actually calculates slower than the others. I have often wondered if that was true. Someone else suggested to me recently that the 2nd option actually performs quicker. I decided that I should find out!
Buried deep in the Document Properties of a QVW, on the Sheets tab, there is a list of all the objects on the sheet along with their last calculation time (CalcTime) in milliseconds. If you looked at this for one of your charts, you might find that it says "0". This is because the last calculation might have been from cached data so the only accurate CalcTime is when the chart is first initialized in QlikView or when the first of a particular set of selections are made in a recently opened document - where the cache has not been established yet.
So, what I did was to take a document with about 5,000,000 sales transaction rows and build a simple Straight Table of Sales / Costs / Margin by Country. I made 3 copies of this document, each copy making the Margin calculation in one of the above ways. The only object in each document is the straight table.
I then completely closed QlikView. I opened one of the documents, recorded the "Initialize" CalcTime for the chart, made a selection of 2 countries (UK and USA) and recorded the "Selection" CalcTime for the chart. Then I shut down QlikView again and repeated. I did this 8 times for each of the documents, opening them in random order.
Here are the results:
| Option 1 | Option 2 | Option 3 | ||||
|---|---|---|---|---|---|---|
| Initialize | Select | Initialize | Select | Initialize | Select | |
| 219 | 125 | 219 | 125 | 219 | 125 | |
| 250 | 140 | 250 | 125 | 202 | 125 | |
| 234 | 125 | 265 | 156 | 203 | 124 | |
| 218 | 125 | 219 | 125 | 218 | 125 | |
| 250 | 125 | 218 | 141 | 218 | 140 | |
| 219 | 125 | 281 | 156 | 250 | 156 | |
| 234 | 141 | 218 | 141 | 219 | 140 | |
| 219 | 125 | 218 | 125 | 297 | 141 | |
| Mean | 230.4 | 128.9 | 236.0 | 136.8 | 228.3 | 134.5 |
| Mode | 219.0 | 125.0 | 218.0 | 125.0 | 219.0 | 125.0 |
| Median | 226.5 | 125.0 | 219.0 | 133.0 | 218.5 | 132.5 |
There is some slight differences. But, statistically, I would have to say that there is no real difference between the 3 options!
One thing that I would say is that I don't like using the Column() function in a Straight Table. This is because the user may have the option to swap columns around and that would invalidate the expression. Other than that, it appears that you can just use whatever option you like and it won't make any difference to QlikView performance.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
Old home, new address - Welcome to qliktips.com
I have been thinking about this for a while and decided that there was no reason not to "brand" my blog a little. So, welcome to qliktips.com.
The site is still hosted by BlogSpot.com (Google) and all the old content is still available and all the old links are still working.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
The site is still hosted by BlogSpot.com (Google) and all the old content is still available and all the old links are still working.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
Wednesday, 17 April 2013
Coming soon, to a bookstore near you...
Today is a significant milestone for me.
Back in January, I was approached by Packt Publishing to author a book for them. They wanted to follow up on the success of Barry and Mike's QlikView 11 for Developers with an addition to their Cookbook series.
A Cookbook is something very different than Barry & Mike's excellent book. Their book is an excellent instructional manual for teaching beginners, and even more experienced developers, how to create QlikView applications. They start at the basics and move through the book to the more advanced subjects.
Like an actual cookery book, I will assume that the user knows how to do certain things, like how to create a new QlikView document and edit the script. I will say, "edit the script", in the same way that the cookery book will say, "preheat the oven to 350F", and I will assume that you will know how to do that.
The Cookbook will be a collection of self-contained recipes collected in broad categories. You don't need to read the book from start to finish, a user can dive into any recipe without having to have completed any before (mostly!)
Right now, the categories are:
- Charts
Today is significant because I have just delivered the final first draft to the publishers. Now, this doesn't mean that the book will be in the stores next week, but it is a significant step along the way.
The next few months will, I am sure, be interesting, but I can't wait.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
Back in January, I was approached by Packt Publishing to author a book for them. They wanted to follow up on the success of Barry and Mike's QlikView 11 for Developers with an addition to their Cookbook series.
A Cookbook is something very different than Barry & Mike's excellent book. Their book is an excellent instructional manual for teaching beginners, and even more experienced developers, how to create QlikView applications. They start at the basics and move through the book to the more advanced subjects.
Like an actual cookery book, I will assume that the user knows how to do certain things, like how to create a new QlikView document and edit the script. I will say, "edit the script", in the same way that the cookery book will say, "preheat the oven to 350F", and I will assume that you will know how to do that.
The Cookbook will be a collection of self-contained recipes collected in broad categories. You don't need to read the book from start to finish, a user can dive into any recipe without having to have completed any before (mostly!)
Right now, the categories are:
- Charts
- Layout
- Set Analysis
- Advanced Aggregation
- Advanced Coding
- Data Modelling
- Extensions
- Useful Functions
- Script
- Improving Performance
- Security
Some categories only have a couple of recipes, some have many. Right now, there is a total of 79 recipes. This could, of course, change before publication.
The next few months will, I am sure, be interesting, but I can't wait.
Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond
Subscribe to:
Posts (Atom)