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.