topic Re: Set Analysis to include zero value in QlikView

Set Analysis to include zero value

prasad — Mon, 18 Jul 2016 04:45:42 GMT

Hi,

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

Sample data:

Date	Branch	Sales
05/03/2015	US	0
06/03/2015	US	2000
07/03/2015	US	2500
05/03/2015	Germany	5000
06/03/2015	Germany	1000
07/03/2015	Germany	0

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:

Date	Branch	Total Sales
05/03/2015	US	0
06/03/2015	US	2000
07/03/2015	US	2500
05/03/2015	Germany	0
06/03/2015	Germany	0
07/03/2015	Germany	0

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:

Date	Branch	Total Sales
06/03/2015	US	2000
07/03/2015	US	2500

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:

Date	Branch	Total Sales
05/03/2015	US	0
06/03/2015	US	2000
07/03/2015	US	2500

Table B:

Date	Branch	Total Sales
05/03/2015	Germany	5000
06/03/2015	Germany	1000
07/03/2015	Germany	0

Can someone please guide me on how to achieve this?

Thanks in advance.

Re: Set Analysis to include zero value

Anonymous — Mon, 18 Jul 2016 07:53:30 GMT

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

Re: Set Analysis to include zero value

MK_QSL — Mon, 18 Jul 2016 08:09:23 GMT

Create a Straight/Pivot Table

Dimension

Date

Branch

Expression

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

Presentation

Untick Suppress Zero Values

For Germany,

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

Re: Set Analysis to include zero value

sunny_talwar — Mon, 18 Jul 2016 08:49:12 GMT

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

Re: Set Analysis to include zero value

sunny_talwar — Mon, 18 Jul 2016 08:52:24 GMT

Sample attached:

Re: Set Analysis to include zero value

prasad — Mon, 18 Jul 2016 09:11:10 GMT

Thank you Sunny, it works for me.