Set Analysis to include zero value

[prasad]

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.

[Anonymous]

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]

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 · Accepted solution]

Sample attached:

[prasad]

Thank you Sunny, it works for me.