Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
prasad
Contributor III
Contributor III

Set Analysis to include zero value

Hi,

I have a situation here where I want to show the zero values on sales data.

Sample data:

DateBranchSales
05/03/2015US0
06/03/2015US2000
07/03/2015US2500
05/03/2015Germany5000
06/03/2015Germany1000
07/03/2015Germany0

I have created a pivot table, with Date and Branch as dimensions, and Total Sales as expression. I have used set analysis for expression. For instance, Sum({$<[Branch]={"US"}>} [Sales]).

If I Don't "checked" the suppress-zero option, the result pivot table will be something like below:

DateBranchTotal Sales
05/03/2015US0
06/03/2015US2000
07/03/2015US2500
05/03/2015Germany0
06/03/2015Germany0
07/03/2015Germany0

This is not the desired result because the desired result should only show all US branch's Total Sales.

So, If i "Checked" the suppress zero option, the zero dollar sales will totally be omitted. For instance:

DateBranchTotal Sales
06/03/2015US2000
07/03/2015US2500

Branch Germany did not show up in the table. However, for date 05/03/2015, the zero total sales was omitted.

The desired table i want to achieve as below:

Table A:

DateBranchTotal Sales
05/03/2015US0
06/03/2015US2000
07/03/2015US2500

Table B:

DateBranchTotal Sales
05/03/2015Germany5000
06/03/2015Germany1000
07/03/2015Germany0

Can someone please guide me on how to achieve this?

Thanks in advance.

1 Solution

Accepted Solutions
sunny_talwar

5 Replies
galax_allu
Specialist
Specialist

Hi ,

Hi

Uncheck the 'Supress Zero-Values' in preesnation tab

For TableA:

Sum({$<[Branch]={"US"}>} [Sales])+sum( {1-$} Sales )

For Table B:

Sum({$<[Branch]={"Germany"}>} [Sales])+sum( {1-$} Sales )

Note :

sum( {1-$} Sales  :returns sales for everything excluded by the current selection

MK_QSL
MVP
MVP

Create a Straight/Pivot Table

Dimension

Date

Branch

Expression

SUM({<Branch = {'US'}>}Sales)

Presentation

Untick Suppress Zero Values

For Germany,

SUM({<Branch = {'Germany'}>}Sales)

sunny_talwar

You can try with a calculated dimension for Branch.

If(Branch = 'US', Branch)

and then select 'Suppress When Value Is Null' for the Branch dimension on the dimension tab

sunny_talwar

prasad
Contributor III
Contributor III
Author

Thank you Sunny, it works for me.