Pivot tables not showing all dimension values

[gussfish]

I think this is a bug, since no-one's found a solution in other forums, but a second opinion would be good.  I'm working with:

QV: QlikView for Windows 10.00.8811.6.SR1

OS: Windows XP 2002 SP  2

PC: Intel Core2 Duo, P9400 @ 2.4GHz, 3.45 GB

In short: pivot tables don't seem to respect the Show All Values flag when more than 1 dimension is involved.

In the attached QVW, I've constructed data that (for the sake of example) lists countries, customers, and their stage in the sales pipeline.

If I do a Pivot merely mapping a single dimension ( Stage -> Count(Customer), in the sample), with the following options selected:

• Dimension [Stage] > Show All Values = checked
• Dimension [Stage] > Suppress When Value is Null  = checked
• Presentation [Stage] > Suppress Zero Values = unchecked
• Presentation [Count(Customer)] > Suppress Zero Values = unchecked

then is always shows all Stages in the table, no matter what Country or Stage is selected in the selectors.

So far so good!

But if I now add a second dimension, so that it maps ( (Stage,Country) -> Count(Customer) ), with the same options selected, then it now will only show those Stages (or Countries, for that matter) that have non-zero, non-null values according to the selections.  e.g. If stage LMN is selected, then only the LMN row and the Country A column is displayed. 

Of course, unchecking Support When Value is Null 'fixes' this, but then shows rows or columns with nothing but zeros or nulls, and I don't want to see such rows anyway.  The factor tipping my opinion in the direction of considering this to be a QlikView bug rather than merely being a new feature to request, though, is the observation of it having differing behaviour between when there is 1 dimension vs when there are 2 dimensions.

Regards,

Angus.

[s_uhlig]

Hi Angus,

would you expect that "Show All Values" on the first dimension takes priority over "Suppress When Value Is Null" on the second dimension?

[gussfish]

Hi Uhlig,

I can't see why those two settings would potentially be in conflict and be needing any sort of prioritisation. 

Let's work with an example.  Support my data included this record in addition to what's in my sample app:

(COUNTRY,STAGE,ID,CUSTOMER)=(null(),'LMN',1,'JOHN')

Starting with "Show All Values" on the first dimension of the 2D pivots (i.e. on STAGE), this insists that 'LMN' will be shown no matter what the selectors are.  So, I'd expect that there will be an 'LMN' row.  'Suppress When Value is Null' on the second dimension (COUNTRY) says to not show the COUNTRY=null() ordinate.  So, I'd expect that there will not be a '-' column.  Consequently, my additional record will not contribute to any figure in the pivot table.

If I take these around the other way i.e. Suppress the null column but show the LMN row, I believe I get the same result.

So, am I missing something that your question is implying?  What's prompted you to ask this question?

Regards,

Angus.

[s_uhlig]

Hi Angus,

Afaik "Suppress When Value is Null' hides all "rows" where the corresponding dimension is null. So on the first one you tell qv to "show me all rows" and on the second "oh no, just these, where I'm not null" - therefore I think, the "suppress setting" on the second dimension takes priority over "show all" on the first one.

Hope I don't misunderstand you.

Sven

[gussfish]

Hi Sven,

yes, I think I see what you're saying, and I so far I think we're in wild agreement, though I'm not seeing that it affects the validity of my original defect report.  Yet, the existence of this discussion suggests that you're not agreeing with something.

So, let's relate it to my sample app, with these settings:

• Dimension [Stage] > Show All Values = checked
• Dimension [Stage] > Suppress When Value is Null  = checked
• Dimension [Country] > Show All Values = checked
• Dimension [Country] > Suppress When Value is Null  = checked
• Presentation > Suppress Zero Values = unchecked

If I select all Stages except ABC, so that there are no records selected with non-null Country C values, I'm

expecting

Here, ABC is still shown because I still have "Show All Values" set for Stage, but there is no row for the null Stage since we've set "Suppress When Value is Null"; likewise Country C is still shown for the same reason but there's no column for the null Country. 

Do you beg to differ with the above?

Regards,

Angus.

[gussfish · Accepted solution]

QlikView have now accepted this as a defect and assigned it Bug ID 39227.

Angus.