Hide last row of pivot table when null values

[#1]

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.

[marcus_sommer]

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

- Marcus

[Anonymous]

Like that??

[Anonymous]

Not like that LOL

[#5]

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

[#7 · Accepted solution]

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.