topic how can I show all rows in a pivot table even if there are zeros in the data? in QlikView

how can I show all rows in a pivot table even if there are zeros in the data?

Tue, 21 Oct 2014 20:17:47 GMT

Hi all,

Ive created a pivot table that has some rows that do not display if there are zeros for all the expressions.

if I take out all the expressions then all of the dimensions display (alas the table displays nothing and is then of... shall we say... limited usefulness).

as soon as I create an expression, if there are no items that meet the criteria, the row does not display.

I have created a sub tables of all the values for the dimensions, I have made sure that display all values in the dimesion tab is checked, supress zero values in the presentation tab is unchecked, and populate missing cells is checked.

Really frustrated on this one.

any Ideas?

Re: how can I show all rows in a pivot table even if there are zeros in the data?

trey_bayne — Tue, 21 Oct 2014 20:20:34 GMT

Uncheck the Suppress Zero-Values box under the Presentation tab of the Pivot table.

Re: how can I show all rows in a pivot table even if there are zeros in the data?

ashfaq_haseeb — Tue, 21 Oct 2014 20:22:39 GMT

And for dimension select show all values.

Regards

Ashfaq

Re: how can I show all rows in a pivot table even if there are zeros in the data?

Tue, 21 Oct 2014 20:23:41 GMT

as I said:

"I have created a sub tables of all the values for the dimensions, I have made sure that display all values in the dimesion tab is checked, supress zero values in the presentation tab is unchecked, and populate missing cells is checked."

Re: how can I show all rows in a pivot table even if there are zeros in the data?

Tue, 21 Oct 2014 20:25:25 GMT

show all values checked Ashfaq... no luck.

But thanks for replying

Re: how can I show all rows in a pivot table even if there are zeros in the data?

albertovarela — Tue, 21 Oct 2014 20:27:02 GMT

Untick the Suppress Zero-values on the Presentation tab

Re: how can I show all rows in a pivot table even if there are zeros in the data?

trey_bayne — Tue, 21 Oct 2014 20:28:28 GMT

Thomas,

Sorry, I didn't read the whole post. You already tried what I suggested. Can you post the app? If not, screen shots of what you are seeing may be helpful as well.

Re: how can I show all rows in a pivot table even if there are zeros in the data?

Anonymous — Tue, 21 Oct 2014 20:28:46 GMT

Add some useful (or unuseful) attribute to the dimension for which you want all rows displayed.

Add that attribute as an average value expression.

We do this for Stores sometime so I use Avg(SqFt) as an expression. As long as the store is not filtered out through other selections, this always shows me rows.

Re: how can I show all rows in a pivot table even if there are zeros in the data?

ashfaq_haseeb — Tue, 21 Oct 2014 20:28:47 GMT

Hi,

if possible can you share sample app

Regard

ASHFAQ

Re: how can I show all rows in a pivot table even if there are zeros in the data?

Tue, 21 Oct 2014 20:30:24 GMT

Re: how can I show all rows in a pivot table even if there are zeros in the data?

ashfaq_haseeb — Tue, 21 Oct 2014 20:32:51 GMT

Hi,

Can you do that for all dimensions.

Regards

ASHFAQ

Re: how can I show all rows in a pivot table even if there are zeros in the data?

Tue, 21 Oct 2014 20:35:39 GMT

Thanks Trey... its cool... seems like It should be a quick fix.

Re: how can I show all rows in a pivot table even if there are zeros in the data?

Tue, 21 Oct 2014 20:39:33 GMT

oh and if I uncheck suppress null for the first dimension I get everything but in a weird place like so

Re: how can I show all rows in a pivot table even if there are zeros in the data?

Wed, 22 Oct 2014 16:21:35 GMT

@Trey Bayne,

I figured out a way to do this... it's ugly but it works.

I added an expression:

(ISNULL(MB_DQ_ISSUES_4),1)

this puts a '1' in that column for every row

I minimized the column, made the text white, and made a single space the header name the pivot looks slightly padded on the right side but I see all my rows in the right position.

Thanks for your response! Helped the gears grind.

Cheers,

Thomas