topic Pivot table not showing all values in QlikView

Pivot table not showing all values

Tue, 29 Nov 2011 21:53:08 GMT

My pivot table isn't showing all my values for each month and i can't figure out why.

attached is qvw. --pivot table on sheet1

My table box shows all the correct data. When i select a certain "PhaseDesc" in my table box then the pivot table shows the correct months but the pivot table won't show the full dataset unless a selection is made in table box.

Re: Pivot table not showing all values

johnw — Tue, 29 Nov 2011 22:16:44 GMT

You have PhaseDesc as an expression. So you'll only see a single PhaseDesc for any combination of Project, MajorFeature and Month. You will ALSO only see it if that PhaseDesc is UNIQUE for that month. QlikView doesn't know what you want it to do when there are multiple, so it's returning null, which is why you aren't seeing those months.

What do you WANT the chart to show?

One possiblity would be to see all of the PhaseDescs in a single cell. For that:

concat(distinct PhaseDesc,'
')

And leave enough room for them all. Charts won't autosize the cells to fit the content. But then, that won't work with your colors. It seems like you want one cell per PhaseDesc. Maybe you want it as a dimension? Then you just get striped rows and a lot of blanks. Hmmm, concat(PhaseDesc) fixes the colors, but of course there are still lots of blank cells.

You could use a sequence number and then display that sequence of the available PhaseDescs. You'd add a dimension of valueloop(1,7), say, then this as the expression:

subfield(concat(distinct PhaseDesc,','),',',valueloop(1,7))

There's probably a simpler expression, but I'm not thinking of it for some reason. That messes up the colors. To fix them, label your expression PhaseDesc. But I still have no idea if this is what you want.

See attached.

Pivot table not showing all values

Tue, 29 Nov 2011 22:50:59 GMT

Well, that's pretty cool! I think anyone of those could do the trick!

Thanks!

Re: Pivot table not showing all values

LizWhit — Wed, 02 Sep 2020 15:40:07 GMT

Hi, I have used your ValueLoop solution which was just what I was looking for. However, I would like to add conditional formatting to the background colour based on another field which is not in the pivot table (this worked ok in a basic pivot table), but it adds the formatting to all the cells in a row rather than just the relevant ones. Do you have any advice? Thanks