Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
MVP
MVP

Re: Pivot table not showing all values

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.

2 Replies
MVP
MVP

Re: Pivot table not showing all values

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

Pivot table not showing all values

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

Thanks!