topic Re: Charting a summary dimension with no values in QlikView

Charting a summary dimension with no values

gussfish — Fri, 13 May 2011 00:41:30 GMT

Hi folks,

I have a table of potential customers, various customer categorisations like country & industry (which are presented in the UI as selectors), and their stage in the relationship-development pipeline (e.g. Identified, Contacted, Presented-to, Negotiating, Won, Lost).

When I graph the number of customers per stage (i.e. chart of Stage -> Count(Customer)), it is possible that a stage might have no customers and therefore QlikView by default omits this stage from the x-axis. How can I ensure that the stage is still on the axis, even when there is no data for it?

The only way I've found to force this is to introduce artificial records into the data table i.e. something like

LOAD 
     Null() As Customer, 
     Stage
Inline [Stage
Identified
Contacted
(etc)];

but modified so that a record is produced not only for every Stage, but every combination of Stage, country, industry etc. This seems very kludgy, though. Is there a better way? I've tried playing with the 'Show All Values' and 'Show nulls' checkbox on the dimension page of the chart properties box, to no avail.

Thanks,

Angus.

Re: Charting a summary dimension with no values

erichshiino — Fri, 13 May 2011 02:42:10 GMT

Hi,

I created a small sample application for this.

You used show all values, but you also need to go to the chart properties -> Presentation tab -> unckek supress zero-values

You will probably need to check supress when value is null on the dimensions tab.

Please, check attachment.

Hope it helps,

Erich

Re: Charting a summary dimension with no values

gussfish — Mon, 16 May 2011 23:35:14 GMT

Hi Erich,

thanks for those comments. I've come to realise, though, that my difficulty arises in a pivot table with >1 dimension.

Hence, on one hand, I found that if I changed your 3rd chart into pivot table, then it (correctly) showed all dimension values no matter what was selected (see the chart numbered 5. in the attachment).

But on the other hand, when I then added Country as a second dimension to that pivot table, then my problem manifested i.e. it only shows the dimensions that have at least one customer (see chart 6. in the attachment).

Is this solvable? Or is it a possible QlikView defect? Or am I still missing something?

Thanks,

Angus.

Re: Charting a summary dimension with no values

Fri, 20 Apr 2012 16:35:25 GMT

One way is to add + sum({1}0) to your expression - it adds zero, but i tforces the SUM expression to be evaluated in all dimensions, thereby pushing all dimensions into your chart. See attached example - I modified yours.