Set Analysis Values change per selection

[qlikwiz123]

I have three text boxes to show 3 different values.

Field = Category

Values = A,B,C

Each text box has a set analysis to show A, B, C values respectively. But whenever a selection is made on 'Category', the other two values should turn 0 in the boxes and only the selected value box has to show a value. I solved it by writing an 'if' conditon for GetFieldSelections.

But this is creating another problem. For example, when A is selected, the other two boxes show 0 but A shows 100 and not the actual value. Same with B and C. How do I show the  actual value even when a filter is applied on 'Category' field

[QFabian]

Hi @qlikwiz123 , you have 3 expressions like these : ?

sum({<Category = {'A'}>} Field)
sum({<Category = {'B'}>} Field)
sum({<Category = {'C'}>} Field)

[qlikwiz123]

I have something like below.

TextBox A:

if(GetFieldSelections(Category)='B' or GetFieldSelections(Category)='C', '0',

if(GetFieldSelections(Category)='A', sum({<Category = {'A'}>} Field),

sum({<Category = {'A'}>} Field)

)

TextBox B:

if(GetFieldSelections(Category)='A' or GetFieldSelections(Category)='C', '0',

if(GetFieldSelections(Category)='B', sum({<Category = {'B'}>} Field),

sum({<Category = {'B'}>} Field)

)

TextBox C:

if(GetFieldSelections(Category)='A' or GetFieldSelections(Category)='A', '0',

if(GetFieldSelections(Category)='C', sum({<Category = {'C'}>} Field),

sum({<Category = {'C'}>} Field)

)

This way, whenever a selection is made on Category, only the selected value's box shows a value and other two boxes show 0. This part works but when a value is selected, that box shows 100 instead of original calculation.

[QFabian]

@qlikwiz123 , please check if this example is what are you looking for. It uses SubStringCount and Concat functions.

script:

Load * INLINE [
Category, Field
A,1
B,1
C,2
];

Textboxs expressions :

=if(SubStringCount(Concat(Category, '|'), 'A') = 1, sum({<Category = {'A'}>} Field), 0)

=if(SubStringCount(Concat(Category, '|'), 'B') = 1, sum({<Category = {'B'}>} Field), 0)

=if(SubStringCount(Concat(Category, '|'), 'C') = 1, sum({<Category = {'C'}>} Field), 0)

SubStringCount

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/StringF...

Concat

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/ChartFunctions/St...

[QFabian]

Check the few changes in your formula, you must include set analysis in the divisor part of the formula too

=if(SubStringCount(Concat(Category, '|'), 'A') = 1,((Count(Distinct {$<Category={'A'}>} Code)/
Count(Distinct {$<Category = >} Code))), 0)

[qlikwiz123]

@QFabian 

What happens if I have multiple rows of the same categories (More than one A, B, C)? How do I make this dynamic instead of equaling it to 1?

[QFabian · Accepted solution]

@qlikwiz123 , qlik works with aggregations (sum, count, avg) to aggregate data depending on selections, besides, expressions are evaluted row by row, then aggregate results.

Please change the formula : '= 1' to '> 0'

=if(SubStringCount(Concat(Category, '|'), 'A') > 0, sum({<Category = {'A'}>} Field), 0)

[qlikwiz123]

I will have new data added and the count of Category values A, B, C will not stay 1, making the above expression to fail. I am just wondering how to make this dynamic and make it work.

[qlikwiz123]

Perfect. Thank you.