left side of the comparison for set modifiers

[#1]

Hi

From what I understand, only fields are valid on the left side of the comparison for set modifiers but are you able to wrap them in a function to force format.

The reason I ask this - I have a month filed which is a dual value and I comparing this against the current month - 1.  I wanted to force the dual month to the num() to make sure the comparison is correct.

e.g.

sum({$<[Receipt Year] = {'=$(vCurrentYear)'},num([Receipt Month]) = {'<=$(vLastMonth)'}>}[# Currency line value]))

I know I can over come this in the script but I am interest to know the 'rules' of set analysis a bit more.

Many thanks

Oli

[tresesco]

You have to do it otherway. Force the compared value to be of similar format as in the field([Reciept Month]). Write the expression for the variable vLastMonth to fit the format.

Update: Say, your reciept field has got values like, 'Jan', 'Feb' .... and you are calculating VLastMonth from another field which is like 1, 2, 3..

Then define:

vLastMonth=Month(Date#(NumericMonthField,'MM'))

[Oleg_Troyansky · Accepted solution]

I found that sometimes it might be easier to formulate a Search criteria, using the same field:

sum({$<[Receipt Year] = {'=$(vCurrentYear)'}, [Receipt Month] = {"=num([Receipt Month])<=$(vLastMonth)"}>}[# Currency line value]))

You still use the Field Name at the left side of the Modifier, but now you are free to use any formatting function in the search criteria itself. The key addition here is the equal sign in front of the Search condition.

I'm teaching those nuances in a great detail at the Masters Summit for QlikView.

cheers,

Oleg Troyansky