topic set analysis question in QlikView

set analysis question

Anonymous — Thu, 24 Dec 2015 09:06:22 GMT

Hi Guys,

Why is that I need to create a new field in order for a chart to display a 0 instead of ignoring my selection.

For example:

I have the following expression:

sum({<CompanyName={'A'}>} Sales)

If I select CompanyName B, I would expected that qlikview would return 0 and not ignore my selection

Only if I use a new field CompanyName2 and select B, then Qlikview would return 0.

Do I always have to work that this or is there a simple way so qlikview can retrun 0 without creating a new field.

I've attached my app.

Cheers

Sam

Re: basic set analysis question

MayilVahanan — Thu, 24 Dec 2015 09:21:36 GMT

Try like this

=if(CompanyName = 'A', Sum(Sales), 0)

Uncheck Suppress Zero Values in Presentation Tab.

Re: basic set analysis question

tresB — Thu, 24 Dec 2015 09:24:43 GMT

If you want the expression return always '0' when anything else (apart from 'A') is selected and value only when 'A'/nothing is selected, try with intersection operator('*') like :

sum({<CompanyName*={'A'}>} Sales)

Re: basic set analysis question

Thu, 24 Dec 2015 09:25:34 GMT

Expression as :

=if(CompanyName = 'A', Sum(Sales), 0)

Re: basic set analysis question

Anonymous — Thu, 24 Dec 2015 09:28:43 GMT

that was exactly what i was looking for. Better than creating new fields.

Re: basic set analysis question

Peter_Cammaert — Thu, 24 Dec 2015 09:30:58 GMT

I think you're missing something about Set Analysis.

In your example, you replace the current CompanyName selection with a forced selection as detailed by the set modifier <CompanyName={'A'}>. This is only temporarily (for this object) and for this expression.

The behaviour you expect can be accomplished by enabling the checkbox "Show All Values" in Properties->Dimensions and by disabling the checkbox Suppress Zero-Values in Properties->Presentation.

But then, what is the use of displaying all dimension field values and showing only aggregation results for a single company as specified by the set specification?

Re: basic set analysis question

Anonymous — Thu, 24 Dec 2015 09:33:27 GMT

Only think I curious is why does qlikview underline the expression in red. It gives the impression that the expression is incorrect.

Re: basic set analysis question

Peter_Cammaert — Thu, 24 Dec 2015 09:35:48 GMT

Weird. You will still not get any other dimension values because zeroes will be suppressed by default in your chart. And if you disable zeroe suppression, you'll get a NULL value in your dimension column.

Re: basic set analysis question

Peter_Cammaert — Thu, 24 Dec 2015 09:37:05 GMT

Don't worry; there are some set analysis constructs that are too smart even for the syntax checker. As the line above your expression states, the Expression is OK.