set analysis question

[Anonymous]

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

[MayilVahanan]

Hi

Try like this

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

Uncheck Suppress Zero Values in Presentation Tab.

[tresesco · Accepted solution]

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)

[#4]

Expression as :

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

[Anonymous]

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

[Peter_Cammaert]

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?

[Anonymous]

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

[Peter_Cammaert]

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.

[Peter_Cammaert]

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.