Display all values of Dimension in a Pivot table

[#1]

Hello,

Im having some problem displaying all the values of a dimension when the expression is null. I want to display the whole list.

From the presentation tab if I select the option to delete null values, I get unwanted columns. Also tried using {1} in the expression.

Any suggestions?

Attached an example.

Thank you so much in advance!

[jonathandienst]

Hi

This problem is often caused by the data model - the dimensions and expression fields are in different tables and one of the expression fields does not have a value against that dimension value. For example, if you have a dates table, and a sales table - create a chart for sales with dimension "Date" and expression "Sum(Sales)". If there are no records in Sales corresponding to the date, then this date will not appear on the chart at all.

There are two solutions - one is to create one dummy sales transaction (to continue the example) with a value of 0 for each possible date. The other is to create a data island* with a copy of the dimension. Use the island field as a dimension and a sum(if(Date = Is.Date, Sales)) type of expression. The first option will perform better with large data sets, while the second has the advantage of not creating fake dummy transactions.

HTH

Jonathan

[shawn-qv]

What is your expected result?

I would also suggest that you create another (cleaner) Year field for reporting, because the current Year column has many variations, and it is treated as text.

[#4]

Thanks for the explanation!

I guess ill try it out with the Data Island. would you have an example? i

didnt get what you meant by Island field.

Thank you!

On Jan 31, 2014 12:46 PM, "Jonathan Dienst" <qcwebmaster@qlik.com>

[#5]

Infortunately this field has to contain text....

[jonathandienst]

Hero warrior wrote:

Thanks for the explanation!

I guess ill try it out with the Data Island. would you have an example? i

didnt get what you meant by Island field.

Thank you!

On Jan 31, 2014 12:46 PM, "Jonathan Dienst" <qcwebmaster@qlik.com>

The field in the island dimension. So if your current dimension is [Date], create an island with the field Is.Date and use that field.

A data island is one or more tables (usually one) that is not linked to the rest of the model. You can create something like this:

DateIsland:

LOAD DISTINCT [Date] As Is.Date

Resident Sales;

Or if you have a master calendar:

DateIsland:

LOAD DISTINCT [Date] As Is.Date

Resident Calendar;

HTH

Jonathan

[shawn-qv]

That's fine if you must keep the Year as text, but what are you expecting to see in the table? How do you want to handle the zeros?

[Anonymous]

Hi

A bit of a bodge, but try using this as your expression :

     sum([Target (%)]) + (max({1} [Target (%)])  * 0)

It forces what would otherwise be evaluated as null to become zero.

Warning:     This will very often work ok. But as Jonathan Dienst mentions earlier in this thread, if the dimension in question is not visible at all in the selected subset of data then you won't have anything possible to evaluate zero against, so you'll need to generate dummy data.

     ********************************

If you could convert your Year into a QlikView year format, then I could improve the Set Analysis to give better presentation.  Or if you must keep it as is, then adding an additional field with Year as a true QlikView year would suffice.

Your current Year text has line feeds in it, which does not help matters at all.  You can see this if you set your object to Wrap Header Text and set Header Height to 2 lines.

Best Regards,     Bill

[#9]

Hello Bill,

Thank you for the suggestions.

I tried with the expression you suggested but it dit not work.

The aim is actually to get a background colour for the dimension.

And for null values there is no background colour.

Attached is the example.