Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Hide last row of pivot table when null values

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:

Pb_pivot_table.jpg

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:

Pb_pivot_table2.jpg

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.

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

6 Replies
marcus_sommer

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

- Marcus

Anonymous
Not applicable
Author

Like that??

Anonymous
Not applicable
Author

Not like that LOL

Not applicable
Author

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

marcus_sommer

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

Not applicable
Author

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.