How to reference a Dual() value in Set Analysis

[#1]

HI

I created the following Dual field - ie Income Statement items have a dual value of 1

if(TB1 = 'Income Statement', Dual(TB1,1),  Dual(TB1,2)) as TB1

This Original Code works:

sum({<FIN_YR_NO={$(v_YearMax)},TB1={'Income Statement'}>} Actual)

This does not (ie replacing the text value with the dual value of 1

sum({<FIN_YR_NO={$(v_YearMax)},TB1={1}>} Actual)

Your help would be appreciated

[swuehl · Accepted solution]

Right, you need to tell QV to use the full set of records in your advanced search instead of the possible values only:

=sum({<TB1 = {"=only({1} TB1) = 1"}>} Actual)

Looking at the length and complexity of this set modifer, it might be easier to write and understand just using

=sum({<TB1 = {'Income Statement'}>} Actual)

(or use an additional field for a kind of short cut, like TB1Num.)

Another possible issue:

Are you using always the same text representation per dual value? I mean, you are not going to assign multiple strings to the same numeric value, right? If you do, I believe this will not work, i.e. you will only keep the first assigned text value.

Regards,

Stefan

[#3]

Only one string value will be assigned to each numeric value, ie Income Statement = 1 and balance sheet = 2

[johannes_myers]

Hi,

Thanks for the suggestion. I was able to use the '=TB1=1' solution in my own app. That being said, I generally don't like using methods that I don't understand 🙂 Can you provide some details as to how exactly Qlik is interpreting that syntax, and why it works in this case?

Thanks,

Johannes

[GregWP]

hi,

hit this issue myself, while a good solution it is a bit naff and slow having to do this. Do you know if Qlik plan on fixing it?

e.g.

SUM({$<OTHER_COST_FLAG={"=Only({1} OTHER_COST_FLAG)=1"},[CAPEX Flag]={"=Only({1} [CAPEX Flag])=0"}>} TRAN_AMT)