topic Re: Re: Select different field values for 2 different fields with same values but different name from 2 listboxes in QlikView

Select different field values for 2 different fields with same values but different name from 2 listboxes

Wed, 17 Sep 2014 18:57:10 GMT

Hi,

I have a chart with following expression formula. I want to set the value of vNumerator variable in the below formula with the value in the first list box and set the value of vDenominator with the value in the second list box. I want to be able to select different values for the same field or different values for 2 different fields (having same value but different field name) from 2 list boxes.

=Sum({$<ACCT_TYPE= {$(vNumerator)}>}INCOME)/Sum({$<ACCT_TYPE= {$(vDenominator)}>}INCOME)

I have tried following:

1. Alternate state works but then my chart does not work since it has to be driven with the same states as the 2 list boxes.

2. Duplicated column ACCT_TYPE and named it as ACCT_TYPE_2. Assigned first list box to ACCT_TYPE and second list box to ACCT_TYPE_2. However, if I select value from first list box having ACCT_TYPE field, the value in second list box gets selected automatically even though the field it holds is ACCT_TYPE_2.

Is there any work around to get the chart working by selecting 2 different values for same field or 2 different fields with same values but different field name ?

Re: Select different field values for 2 different fields with same values but different name from 2 listboxes

Anonymous — Wed, 17 Sep 2014 20:33:43 GMT

vNumerator = GetFieldSelections(field you use for numerator)

It allows to use more than one field selected in the set analysis

Re: Select different field values for 2 different fields with same values but different name from 2 listboxes

Wed, 17 Sep 2014 20:42:22 GMT

Hi,

It is possible through alternate state. Check the attached application.

In my solution, I created on alternate state-GroupA

and then used this expression:

Sum(Income)/ Sum({$< AccountType = GroupA::AccountType >} Income)

Numerator part of expression takes listbox from default state. Hence no identifiers.

For Denominator part, we used GroupA::AccountType to read from GroupA listbox. That does the magic.

P.S. The chart has default state.

Hope this helps you.

Cheers !

Kalpesh Jain

Re: Re: Select different field values for 2 different fields with same values but different name from 2 listboxes

Wed, 17 Sep 2014 21:34:15 GMT

Thanks Kalpesh. I am now able to select different values for same field. However, my chart does not work. I have modified your sample file - added similar chart and more data. On selecting value BBB for AccountType in first list box and value CCC for Accountype in second list box, should display the chart with dimension ID. Please find attached.

Re: Select different field values for 2 different fields with same values but different name from 2 listboxes

Wed, 17 Sep 2014 22:01:39 GMT

Unfortunately, I'm using personal edition so unable to open any applications created on another computer.

But if you can post sample data and screenshots, I might be able to assist more.

Cheers

KalpeshJain

Re: Re: Select different field values for 2 different fields with same values but different name from 2 listboxes

Wed, 17 Sep 2014 23:02:52 GMT

Thanks Kalpesh. Attached is the screenshot and the sample data below. The data is one that you provided just added few more rows for same ID. Can't the personal edition open the file created on another computer at least thrice?

If you see in the screenshot, 2 different values are selected for AccountType from 2 different listboxes. The chart has dimension ID and following expression:

=Sum({$<AccountType = {$(vNumerator)}>}Income)/Sum({$<AccountType = GroupA::AccountType>}Income)

Load * Inline [

Period, ID, AccountType, Income

201401, 1, AAA, 100

201401, 1, BBB, 200

201401, 1, CCC, 300

201401,2, AAA, 200

201401,2, BBB, 100

201401,2, CCC, 400

201401,3, AAA, 300

201401,4, BBB, 400

201401,5, AAA, 100

201401,6, CCC, 200

201401,7, CCC, 100

201401,8, AAA, 500

201401,9, BBB, 100

201401,10,BBB, 900

];

Re: Select different field values for 2 different fields with same values but different name from 2 listboxes

Thu, 18 Sep 2014 06:31:44 GMT

since you are calling a variable in set expression, it should have equal to sign before it (I guess since variable definition is calling a function)

So here is the modified expression:

=Sum({$<AccountType = {$(=$(vNumerator))}>}Income)/Sum({$<AccountType = GroupA::AccountType>}Income)

Alternatively, you may add equal to sign in the variable definition itself and then use the same expression you built

Sum({$<AccountType = {$(=$(vNumerator))}>}Income)/Sum({$<AccountType = GroupA::AccountType>}Income)

But as soon as you put an equalto sign in variable definition, the text boxes will receive evaluated value from default state list box and therefore won't show state specific selections. Without equalto sign in variable definition, variable is evaluated within Chart context.

Regards,

Kalpesh.

Re: Select different field values for 2 different fields with same values but different name from 2 listboxes

Thu, 18 Sep 2014 14:59:25 GMT

This works. Thank you Kalpesh!

Re: Select different field values for 2 different fields with same values but different name from 2 listboxes

Thu, 18 Sep 2014 18:24:34 GMT

Hi Kalpesh,

Is it possible to display only selected rows in the straight table ? For example, I have 2 columns in my table - Employees, Income. I want to display only those rows where Income > 80 000 and Income < 100 000

Thanks,

Sheetal

Re: Select different field values for 2 different fields with same values but different name from 2 listboxes

Thu, 18 Sep 2014 18:50:43 GMT

There are two ways to achieve it. you may choose the one best suits your needs.

1. Set the expression as:- Sum(if (Income > 80000 and Income< 100000, Income))

2. Use calculated dimension as:- if (Income > 80000 and Income< 100000, Employee)

Mark CheckBox "Suppress when value is Null"

Use chart expression as:- Sum(Income)

Regards,

Kalpesh

Re: Select different field values for 2 different fields with same values but different name from 2 listboxes

Thu, 18 Sep 2014 18:52:44 GMT

Ok. Checking "Suppress values when Null" did the trick. Forgot about that

Thank you!

Thanks,

Sheetal