Solved: how can I show all rows in a pivot table even if t... - Qlik Community

Report Inappropriate Content · ‎2014-10-21

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?

Report Inappropriate Content · ‎2014-10-22

@Trey Bayne,

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

I added an expression:

if

(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

View solution in original post

trey_bayne · ‎2014-10-21

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

ashfaq_haseeb · ‎2014-10-21

Hi

And for dimension select show all values.

Regards

Ashfaq

Report Inappropriate Content · ‎2014-10-21

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."

Report Inappropriate Content · ‎2014-10-21

show all values checked Ashfaq... no luck.

But thanks for replying

albertovarela · ‎2014-10-21

Untick the Suppress Zero-values on the Presentation tab

trey_bayne · ‎2014-10-21

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.

Anonymous · ‎2014-10-21

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.

ashfaq_haseeb · ‎2014-10-21

Hi,

if possible can you share sample app

Regard

ASHFAQ

Report Inappropriate Content · ‎2014-10-21

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