More on Dual - Problem in Sets

I have discussed using Dual a couple of times in the past. It can be enormously useful. Sometimes it can throw you a bit - especially if you do not realise that you are using a Dual.

An example comes up in my response to the post of jhoffmann on QlikCommunity.

He was asking about doing calculations in a Set. This is, of course, eminently achievable using the dollar-expansion syntax. You do have to watch it when using a Dual value.

He was using a field called "RollingMonth". I assume, because it comes from the (old) training manual, that this was created in the Script using:

...
Date(MonthStart(date_field), 'MM/YY') As RollingMonth,
...


This syntax creates a Dual value - with the month's date (as an integer) and the text as per the format string.

This is quite fine in a Set if you use a simple calculation such as Max:

Sum({<RollingMonth = {"$(=Max(RollingMonth))"}>} Amount)

You see, Max knows about Dual and will return the correct value so that the comparison will work - it returns the Text value. It is the text value that is required in the Set comparison. If the max month is January 2010, Max will correctly return (in this case) "01/10" - even though it is alphabetically less than the "12/09" before it. Max does the Dual sort on the number and returns the text. Simple.

The problem arises with some other functions that are not so "Dual aware". For example, if we wanted to see the values from the same month last year, we might try this:

Sum({<RollingMonth = {"$(=AddMonths(Max(RollingMonth),-12))"}>} Amount)

The problem is that AddMonths takes a date value as a parameter and returns a date. It doesn't care about the text value. It uses the Dual's numeric value to do the calculation and then returns a date/numeric value. For example, it might be "01/01/2009". This will not work in our Set above (RollingMonth={"01/01/2009"}) because the text doesn't match and the Sum will return 0.

To fix this one, we just need to re-establish the Dual value:

Sum({<RollingMonth = {"$(=Date(AddMonths(Max(RollingMonth),-12),'MM/YY'))"}>} Amount)

Now the calculations will work perfectly.

More on "Sets =- or -=???"

A while ago I blogged about using =- or -= in a set.

I answered a post in QlikCommunity today (http://community.qlikview.com/forums/p/24894/95176.aspx#95176) that demonstrates the use very well.

The question was from sebagiar. He asked how to get an average out of values while ignoring 0s. For example if I have 3 values: 100, 0, 200 then he wants a result of 150, not the 100 that the straight Avg function will give.

My response is to use the following set on the field:

=Avg({< F1 -= {0}>} F1)

So, why "-=" instead of "=-"? Well, the later just tells the field to use all values except 0 - it will exclude any selections on that field. The first one is "additive" (well, I guess I should say "subtractive") and will include and selections on that field and then drop out the zeros.