Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fzalexanderjohn
Creator
Creator

Show zero for no records

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?

Labels (1)
1 Solution

Accepted Solutions
Or
MVP
MVP

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)

View solution in original post

7 Replies
Or
MVP
MVP

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
Creator
Creator
Author

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
MVP
MVP

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
Creator
Creator
Author

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
MVP
MVP

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
Creator
Creator
Author

Hello Or,

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

Or
MVP
MVP

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