Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot table not showing all values

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.

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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.

View solution in original post

3 Replies
johnw
Champion III
Champion III

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.

Not applicable
Author

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

Thanks!

LizWhit
Contributor
Contributor

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