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: 
consenit
Partner - Creator II
Partner - Creator II

Confusing set analysis behavior.

Hello folks.

Let's consider the following table:

Test:

LOAD * INLINE

[

  Dimension, Measure

  A, 1

  A, 0

  A, 1

  B, 1

  B, 0

];

Suppose we want to calculate the sum of "Measure" for dimension A using set analysis:

=Sum({$<Dimension={'A'}>}Measure)

This works OK and calculates the expected value of 2 only if there are no other dimensions selected. However, if we select dimension B the value is still 2 even when we told the formula to take into account the current selection by using the dollar sign.

If we use the IF() function instead:

=Sum(If(Dimension='A', Measure))

The formula correctly displays 2 or 0  depending on the selected dimension.

Is there a way to get the desired behavior using set analysis instead of IF()?

Thanks in advance.

Kind Regards,

Ernesto.

1 Solution

Accepted Solutions
ramoncova06
Specialist III
Specialist III

you can use the following set analysis

=sum({$*<Dimension={'A'}> }Measure)

View solution in original post

8 Replies
sasiparupudi1
Master III
Master III

Remove set analysis form your expression

Sum({$<Dimension={'A'}>}Measure)

and add Dimension as a dimension  in a straight table

ramoncova06
Specialist III
Specialist III

you can use the following set analysis

=sum({$*<Dimension={'A'}> }Measure)

consenit
Partner - Creator II
Partner - Creator II
Author

Hi Ramón.

Thank you, your suggestion worked perfectly.

Kind Regards,

Ernesto.

jonathandienst
Partner - Champion III
Partner - Champion III

There's a simple explanation:

The set expression explicitly selects Dimension={'A'}, so making selections in Dimension will be overridden by tyeh set expression. The $ sign means use the current selections for everything else.

The If() does not change the selections in any way, so if you select B, then the expression will return 0. This is all by design and predictable.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
consenit
Partner - Creator II
Partner - Creator II
Author

Hi Sasidhar.

Thank you, your suggestion works but I need to display the value on a texbox, not a straight table.

Kind Regards,

Ernesto.

consenit
Partner - Creator II
Partner - Creator II
Author

Hi Jonathan.

Very clear explanation, thank you.

Kind Regards,

Ernesto.

qlikviewwizard
Master II
Master II

Hi,

Check this attachment. I think set analysis is not required. You can select any value from

Capture.PNG

Dimension.

johanlindell
Partner - Creator II
Partner - Creator II

You could consider loading the Dimension into two columns and use the second column in your set expression:

LET HidePrefix = '_';

Test:

LOAD Dimension,

     Dimension as _Dimension,

     Measure

INLINE

[

  Dimension, Measure

  A, 1

  A, 0

  A, 1

  B, 1

  B, 0

];

=Sum ({$<_Dimension={'A'}>} Measure)