Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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 ?

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

10 Replies
Anonymous
Not applicable
Author

vNumerator = GetFieldSelections(field you use for numerator)

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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

];

Not applicable
Author

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.

Not applicable
Author

This works. Thank you Kalpesh!

Not applicable
Author

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

Not applicable
Author

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