topic Re: QlikView Set Analysis - Modifier same as a selection in QlikView

QlikView Set Analysis - Modifier same as a selection

Tue, 24 Feb 2015 11:21:24 GMT

Need bit of help. I have constructed an expression in a text box -

(count({$<APPINVESTSTRATEGY={'Divest'}>} distinct ASSET_NAME)) / (count({$} distinct ASSET_NAME))

If I have a list box selection set for the field 'APPINVESTSTRATEGY' as 'Divest', this expression returns 100%. That is as expected.

However, if I have the same field set to 'Invest' (Invest and Divest sets do not have any overlaps), the expression does NOT return 0% as I was expecting. Instead it seems to be calculating the # of Divest assets based upon the the rest of the list box selections and dividing those by count of distinct Invest assets.

Is this expected behaviour? If so, is there anything I can do for the expression to return 0% for the second set of selections (that seems to be the more logical output)?

Re: QlikView Set Analysis - Modifier same as a selection

morganaaron — Tue, 24 Feb 2015 11:49:24 GMT

Yes, that's expected. Your numerator will give you the count of your asset_name field for Divest regardless of your listbox - that's part of set analysis' purpose! What will be changing is your denominator, which as you said will be filtering the count to just 'Invest'.

What is it that you're trying to work out? From your example, this value will either be 100% or 0% all of the time? If so, what purpose is it serving?

Re: QlikView Set Analysis - Modifier same as a selection

jonathandienst — Tue, 24 Feb 2015 11:50:13 GMT

If instead of

count({$} distinct ASSET_NAME),

use

count({1} distinct ASSET_NAME)

count({<APPINVESTSTRATEGY>} distinct ASSET_NAME)

Then the ratio will not be affected by a selection in the APPINVESTSTRATEGY field.

HTH

Jonathan

Re: QlikView Set Analysis - Modifier same as a selection

Tue, 24 Feb 2015 11:57:33 GMT

Hi Anand,

This is expected.

When you give {$<APPINVESTSTRATEGY={'Divest'} in the set analysis, the count() counts records with Divest no matter what current selection is.

That is why the result is not 0.

Even if you dont make any selections, the numerator still counts the no. of records with Divest.

Re: QlikView Set Analysis - Modifier same as a selection

Tue, 24 Feb 2015 11:59:57 GMT

Hi,

Please try the below script

(count(if( APPINVESTSTRATEGY='Divest', ASSET_NAME))) / (count(ASSET_NAME))

Regards,

Joshmi

Re: QlikView Set Analysis - Modifier same as a selection

CELAMBARASAN — Tue, 24 Feb 2015 12:05:42 GMT

Hi,

If you want only about the Divest then use below expr

count({$*<APPINVESTSTRATEGY={'Divest'}>} distinct ASSET_NAME)) / (count({$} distinct ASSET_NAME)

Note: I have used * in the first expr

Re: QlikView Set Analysis - Modifier same as a selection

Tue, 24 Feb 2015 13:30:51 GMT

Thanks for pointing me in the right direction.

Apparently, my set modifier expression was overriding my APPINVESTSTRATEGY selection in the list box. Using the *= operator allowed me to overlay that modifier on top of my current list box selection. Since, in the second situation, both the sets had no overlaps, the result was 0% as expected.

I had only heard about the quick response in the Qlikview forums. Got to experience it first hand today. Many thanks to all those who responded & tried to help.

Re: QlikView Set Analysis - Modifier same as a selection

jldengra — Mon, 09 Mar 2015 12:02:55 GMT

Hello friends,

The provided solution is valid when a single field is refered in the set analysis expression. However it turns into trouble whenever several fields are implied and some of them are intended to ignore their selections, since the star after the dollar sign makes the current selection to be intersected such that the selections desired to be ignored in set analysis are never more ignored since the moment when * is written. So, it works for selections in the first field, but fails ignoring selections for the rest.

For a more general purpose, what is needed is to replicate the field in the model with a different name, and use the original for selection and the replicated one for set analysis. By separating both fields, there is no conflict for QlikView and it can be combined with other set analysis conditions such as ignore selections in other fields, and they still work with this solution.

I hope this helps.