topic Re: Count() in Pivot table in QlikView

Count() in Pivot table

jduluc12 — Thu, 05 Oct 2017 12:56:14 GMT

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?

Re: Count() in Pivot table

sunny_talwar — Thu, 05 Oct 2017 12:57:49 GMT

Have you tried to uncheck 'Suppress Zero Values' on the presentation tab?

Re: Count() in Pivot table

Chanty4u — Thu, 05 Oct 2017 12:58:07 GMT

try this

In dimensions - > tick show all values and in presentation tab ,untick suppress zero values..

and

this also

Did you unchecked it(Suppress when value is null.) for both the dimensions?

Re: Count() in Pivot table

jduluc12 — Thu, 05 Oct 2017 13:08:02 GMT

yes but it still does not show up.

Re: Count() in Pivot table

jduluc12 — Thu, 05 Oct 2017 13:09:18 GMT

i did both but i still do not see

Re: Count() in Pivot table

sunny_talwar — Thu, 05 Oct 2017 13:10:07 GMT

Is the count 0 or is it missing?

Re: Count() in Pivot table

Shubham_Deshmukh — Thu, 05 Oct 2017 13:18:28 GMT

I think there are NULL values instead of 0 values.

Pls check it in your Data source

Re: Count() in Pivot table

jduluc12 — Thu, 05 Oct 2017 13:23:21 GMT

i am doing count which will always return a value.

i am trying to see if there is any product which does not have a salesdate.

Re: Count() in Pivot table

jduluc12 — Thu, 05 Oct 2017 13:38:12 GMT

i do not see any product in the table whose

count(distinct Product.SalesDate) ia 0

Re: Count() in Pivot table

Shubham_Deshmukh — Thu, 05 Oct 2017 13:51:36 GMT

If there is no such value then why you are checking it @Jean

Re: Count() in Pivot table

shanky1907 — Thu, 05 Oct 2017 14:21:47 GMT

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)

Re: Count() in Pivot table

hic — Thu, 05 Oct 2017 14:26:49 GMT

Change your expression to

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

HIC

Re: Count() in Pivot table

Peter_Cammaert — Thu, 05 Oct 2017 14:44:05 GMT

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.

Re: Count() in Pivot table

jduluc12 — Thu, 05 Oct 2017 15:29:19 GMT

Thanks

That works

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

Re: Count() in Pivot table

jduluc12 — Thu, 05 Oct 2017 15:29:35 GMT

Thanks

That works

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

Re: Count() in Pivot table

Peter_Cammaert — Thu, 05 Oct 2017 15:45:06 GMT

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?

Re: Count() in Pivot table

hic — Fri, 06 Oct 2017 07:53:47 GMT

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

Re: Count() in Pivot table

Peter_Cammaert — Fri, 06 Oct 2017 09:22:36 GMT

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.