topic Re: Hide last row of pivot table when null values in QlikView

Hide last row of pivot table when null values

Thu, 10 Dec 2015 19:41:19 GMT

Hello,

I would like to show in a pivot table with two dimensions (SITE and MONTH), an expression for the last 12 months even if there is no data for one month.

The pivot table looks like below:

And I don't find a way to hide the last row of the table.

I tried to tick the option "Supress zero values" in the presentation tab or "Supress if the value is null" for the dimension SITE but the result is:

There is no last row anymore but months with no data for any site are not displayed.

The model is simple.

There are:

a calendar table with all days since the beginning of 2014
a product table
an incident table

The calculated expression is the number of incidents by month and site.

I enclose the QVW and the QVS for a better understanding.

Thanks for your help !

Alex.

Re: Hide last row of pivot table when null values

marcus_sommer — Thu, 10 Dec 2015 19:54:05 GMT

By dimensions you could for each hide NULL what should prevent your last row.

- Marcus

Re: Hide last row of pivot table when null values

Anonymous — Thu, 10 Dec 2015 19:57:09 GMT

Like that??

Re: Hide last row of pivot table when null values

Anonymous — Thu, 10 Dec 2015 20:11:19 GMT

Not like that LOL

Re: Hide last row of pivot table when null values

Thu, 10 Dec 2015 21:52:49 GMT

Hi Marcus,

By doing that, I have not my last row anymore but all 12 months are not displayed.

Only those with data are, like you can see in the 2nd picture I put in my message.

Thanks,

Alex

Re: Hide last row of pivot table when null values

marcus_sommer — Fri, 11 Dec 2015 14:29:10 GMT

It's not possible in this way - are the data like they are you could suppress NULL and get a chart without them or you shows everything and must live with (some unwanted) NULL display.

The only alternative is to clean and adjust your data - filling NULL and missing data with some dummy data and/or create the missing data: How to populate a sparsely populated field

- Marcus

Re: Hide last row of pivot table when null values

Mon, 14 Dec 2015 10:40:35 GMT

Hi,

I found the solution !

By following these steps:

Creation of a table, totally independent of other tables in the model, which contains only a field with all calendar months
Modification of the table dimension for using the created month field
Addition of a condition in the expression in order to force the data month to be equal to the month of the independent calendar: Count(if(Mois2=Mois,ID_INCI))

I know it is not ideal to use a condition in an expression but in this case, it doesn't seem too bad.

Enclosed the solved case.

Thanks for your help,

Alex.