Show zero for no records

[fzalexanderjohn]

I'm preparing a presentation for the performance of our sales people. For example, I needed a number of tables (for different products) that show a list of our sales-people and what they sold in the last 6 months.

The result should look like so:

Sales person	Product	Sales
Peter	A	100
Karl	B	10
Susan	A	200
Martin	C	0

But when I filter the timeframe and the product, all sales people with zero sales disappear in the table

Sales person	Product	Sales
Peter	A	100
Karl	B	10
Susan	A	200

(Martin disappeared because he has no sales records in the selection)

I basically need all 4 sales people to always show up in the table even if the selections return no records and zero for sales in those cases.

Anybody know how this can be achieved?

[Or · Accepted solution]

You could try either of the following - the former ignores all selections and the latter only on specific field(s). Note that "Include zero values" must be checked under Data Handling.

Sum(Sales)+Sum({1} 0)

Sum(Sales)+Sum({< Month= , Product=>} 0)

[fzalexanderjohn]

Hello Or,

thank you for your reply. What I didn't make clear in my original post was, that I have to have a selection for products as well. Sorry for that. We have several thousand products and and I only want to select a small subset of them (say 4) for a particular analysis.

If I use your solution, all products show up... which is expected.

[Or]

What is your expectation in the scenario where products A and B were selected but C was not? Martin's only row is for product C, so if you aren't displayed non-selected products, what would his row(s) look like?

[fzalexanderjohn]

Hello Or,

If C was not selected, C should not show up. So if I did an analysis for just product A, I want to select A, show all 4 sales people but Karl and Susan with zeros (instead of them not appearing).

I think I've also just found a way.

1st Dimension

[Sales people]

2nd Dimension

=if(Match(product, $(=Chr(39) & GetFieldSelections(product,Chr(39)&','&Chr(39),12) & Chr(39) ) ),product)

edit:

I found the solution for this here: https://community.qlik.com/t5/New-to-Qlik-Sense/Getfieldselections-with-multiple-values-in-if-condit...

Measure

sum(
Sales
)+
Sum(
{<Month=, Year=, product= >}
0)

[Or]

If you've found a solution, great. There's quite a few different ways to go about doing something like this and comparing with GetFieldSelections is indeed one of them, though I'd suggest you consider comparing with a concat() of the values instead as it is a more robust option (concat will work if you've implicitly selected the products, for example by selecting Product Group = 'AB' which includes products A and B, but did not explicitly select a product).

If not, I'm still not sure what your expectation is. Since both A and B were selected, are you expecting one 0 row for each salesperson for A and another one for B?

[fzalexanderjohn]

Hello Or,

could you tell me how it would work with concat? I could not figure it out.

[Or]

concat(distinct Field) just replaces GetFieldSelections(Field) inside the match. Otherwise everything stays the same.