Solved: How to reference a Dual() value in Set Analysis - Qlik Community

Report Inappropriate Content · ‎2012-11-15

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 · ‎2012-11-19

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

View solution in original post

Gysbert_Wassenaar · ‎2012-11-15

I don't think it will work with the dual function. You could try this:

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

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

talk is cheap, supply exceeds demand

Report Inappropriate Content · ‎2012-11-15

Sorry, the below code does not work

Any suggestions

------------------------------------------------------------

Thanks Gysbert

I seemed to get this to work, I enclosed the 1 in double quotes {"=1"}

Is this method correct?

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

Report Inappropriate Content · ‎2012-11-15

Does anyone have a possible solution to this, thanks.

Report Inappropriate Content · ‎2012-11-16

Hi guys, any suggestions as to whether this can be done?

swuehl · ‎2012-11-16

I don't understand, I thought you already got a solution using

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

?

Report Inappropriate Content · ‎2012-11-16

Hi

I intially thought it worked, but it didnt - I edited my post.

Can a dual value be referenced in set analysis? If not, then I suppose I'll have to look at another solution, but would really be interested in seeing if someone has a proper solution.

should this work, or am I doing something wrong?

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

Thanks again

Report Inappropriate Content · ‎2012-11-16

Hi Guys - add attached a simple example which I hope shows the issue I am trying to solve,

Thanks

whiteline · ‎2012-11-16

Hi.

I've found only two working ways for reference dual values, by number and by text:

=sum({<TB1={'=TB1=1'}>}Actual)

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

Report Inappropriate Content · ‎2012-11-19

Hi Whiteline

Thanks for your suggestion.

Problem is if I select "Balance Sheet" in my list box, then the formula no longer works. Appears as though the set analysis of "=TB1=1" is not working 100% as it not forcing it to always refering the "Income Statement"

regards