Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. 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.