topic How to reference a Dual() value in Set Analysis in QlikView

How to reference a Dual() value in Set Analysis

Thu, 15 Nov 2012 12:41:24 GMT

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

Re: How to reference a Dual() value in Set Analysis

Gysbert_Wassenaar — Thu, 15 Nov 2012 12:49:40 GMT

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)

Re: How to reference a Dual() value in Set Analysis

Thu, 15 Nov 2012 12:58:26 GMT

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)

Re: How to reference a Dual() value in Set Analysis

Thu, 15 Nov 2012 15:56:54 GMT

Does anyone have a possible solution to this, thanks.

Re: How to reference a Dual() value in Set Analysis

Fri, 16 Nov 2012 14:40:44 GMT

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

Re: How to reference a Dual() value in Set Analysis

swuehl — Fri, 16 Nov 2012 14:47:40 GMT

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

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

Re: How to reference a Dual() value in Set Analysis

Fri, 16 Nov 2012 18:04:08 GMT

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

Re: How to reference a Dual() value in Set Analysis

Fri, 16 Nov 2012 18:21:44 GMT

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

Thanks

Re: How to reference a Dual() value in Set Analysis

whiteline — Fri, 16 Nov 2012 18:43:54 GMT

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)

Re: How to reference a Dual() value in Set Analysis

Mon, 19 Nov 2012 07:00:33 GMT

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

Re: How to reference a Dual() value in Set Analysis

swuehl — Mon, 19 Nov 2012 10:18:11 GMT

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

Re: How to reference a Dual() value in Set Analysis

Mon, 19 Nov 2012 17:38:09 GMT

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

Re: How to reference a Dual() value in Set Analysis

johannes_myers — Mon, 11 Mar 2019 14:29:50 GMT

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

Re: How to reference a Dual() value in Set Analysis

GregWP — Thu, 03 Oct 2019 08:47:06 GMT

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)