topic Re: Confusing set analysis behavior. in QlikView

Confusing set analysis behavior.

consenit — Thu, 10 Sep 2015 14:11:15 GMT

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.

Re: Confusing set analysis behavior.

sasiparupudi1 — Thu, 10 Sep 2015 14:19:53 GMT

Remove set analysis form your expression

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

and add Dimension as a dimension in a straight table

Re: Confusing set analysis behavior.

ramoncova06 — Thu, 10 Sep 2015 14:22:02 GMT

you can use the following set analysis

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

Re: Confusing set analysis behavior.

consenit — Thu, 10 Sep 2015 14:30:35 GMT

Hi Ramón.

Thank you, your suggestion worked perfectly.

Kind Regards,

Ernesto.

Re: Confusing set analysis behavior.

jonathandienst — Thu, 10 Sep 2015 14:33:47 GMT

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.

Re: Confusing set analysis behavior.

consenit — Thu, 10 Sep 2015 14:34:05 GMT

Hi Sasidhar.

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

Kind Regards,

Ernesto.

Re: Confusing set analysis behavior.

consenit — Thu, 10 Sep 2015 14:36:34 GMT

Hi Jonathan.

Very clear explanation, thank you.

Kind Regards,

Ernesto.

Re: Confusing set analysis behavior.

qlikviewwizard — Thu, 10 Sep 2015 14:37:17 GMT

Hi,

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

Dimension.

Re: Confusing set analysis behavior.

johanlindell — Thu, 10 Sep 2015 14:52:13 GMT

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)