Count() in Pivot table

[jduluc12]

Hi,

here is my pivot table

dimension:

Product.Name

Expression:

=count(distinct Product.SalesDate)

The table shows only those products which has at least 1 Product.SalesDate. It does not show the Products which have 0 SalesDate.

Can you think of any reason?

[shanky1907]

Hello,

In the load script, you can create a flag to check the same like:

If(StartDate='',0,1) as Flag_StartDate

Now, on the expression you can just take the count of the Flag as Count(Flag_StartDate)

[hic · Accepted solution]

Change your expression to

=count(distinct Product.SalesDate) + Sum({1} 0)

HIC

[Peter_Cammaert]

Or without any expression magic, just enabling 'Show all Values' & 'Suppress when value is Null' in the Dimensions tab, and disabling 'Suppress Zero-values' in the Presentation tab, we get this:

You're probably doing something more in your document that may change the context. Care to show/tell us?

BTW if you don't want to proceed in dicussion Aggr () inside if, please close it.

[jduluc12]

Thanks

That works

now the mail problem is described in Aggr () inside if.

[jduluc12]

Thanks

That works

now the mail problem is described in Aggr () inside if.

[Peter_Cammaert]

Henric,

what are the pivot table conditions that warrant the use of your expression, instead of just disabling 'Suppress zer-values' in the Presentation tab?

[hic]

Just removing "Suppress zero" will not do the trick. See picture below. But you are right that there is more than one way to solve this.

The question is - why is a dimensional value missing? It could be

1) A selection has excluded it

2) The value is possible, but there are no values of "Amount" (the field in the measure) associated with it

3) The value is possible, but the Sum(Amount) sums up to zero.

My method takes care of all three cases.

Further, the situation gets more complicated if you have more than one dimension...

HIC

[Peter_Cammaert]

Thanks Henric.

If we assume that in more than a few cases, there is no need to undo the active selections (just show all active dimension values, even if they result in 0-value measures), then disabling the "Suppress zero-values" checkbox would do just fine. For a single dimension.

But there are very few pivot tables that have only one dimension. That's where I got stuck trying to find a strategy that covers all possible situations using just the checkboxes. There is some odd behaviour that surfaces when combining multiple dimensions and different checkbox settings, especially with NULL values. Your technique will probably save us a lot of time chasing an otherwise elusive solution.