Raise a flag to the Green, White and Gray

We can sometimes forget just how powerful this feature of QlikView is.  It is so simple - you click on a value and it goes green; anything that is associated with your selection stays white; anything that is excluded by your selection goes gray.  What could be more simple?

From a data discovery point of view, this is an enormous boon.  Other tools allow you to drill down into the data with lots of different options (even Excel - although I am not really impressed with the facility from the much vaunted Tableau!) but QlikView is quite unique in giving you visibility of the excluded values.

Of course, one of the problems with List Boxes is that, to make them really useful, they do take up quite a lot of space on a layout.  I have blogged about different strategies for this in the past - for example, using a container for list boxes - but nothing quite replaces the ability to see a wide range of multivariate data in one place.

For this reason, I quite often create a tab that pretty much just has list boxes.  All the primary fields of information will be represented.

Why is it that we don't sell Women's Footwear to Argentina?

Raise a flag to the Green, White and Gray - just one of the many reasons why I love QlikView.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner
Follow me on Twitter: @stephencredmond

A very rounded function

The Round function in QlikView is similar to the equivalent function in Excel and other tools, but it is much more powerful.

I have used it for years, but rarely thought about its power until recently.

At a simple level, you can call the function like this:

   Round(3.14159)

Which will round the value to the nearest integer, thus giving me a nicely rounded 3.

To round to a significant number of places, instead of telling it the number of places that I want (as you do in Excel), I need to give it a step value to round to.  So, if I want to round to the nearest integer, the step value is 1.  Round to 3 places, the step value is 0.001:

   Round(3.14159, 0.001)

Which gives me 3.142 - the value is rounded to the nearest 0.001.

This is pretty much the way that I have always used the round function.  But you are not mandated to have only 1s in your step value!  How about this:

   Round(3.14159, 2)

This would yield a value of 4 - pi rounded to the nearest 2.

How about this:

   Time(Round(Time#(Times, 'HH:mm'), (1/(24*4))))

Here I a parsing a text field called "Times" that has a time value in "HH:mm" format.  I then round it to the nearest quarter hour.  Would you find that useful for log file analysis?

There is also a third parameter that we can give to Round - an offset.  So, for example, if I want to generate some random numbers:


   Load
Round(Rand()*100, 1) as RandVal
   AutoGenerate(1000000);


This would generate a range of numbers between 0 and 100.  If I look at the spread of those numbers I can see that 0 and 100 have only half of the occurrences of all the other numbers:

This happens, of course, because the range of values that will produce either 0 (0-0.4999) or 100 (99.5-99.9999) are half of the range of values that would produce and other number (e.g. 5 = 4.5-5.4999).

So I will offset each of the values by 0.5 and round those.  This will give me the result I need.

   Load
Round(Round(Rand()*100, 1, 0.5)) as RandVal
   AutoGenerate(1000000);

Simplistic example, but interesting.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner
Follow me on Twitter: @stephencredmond