I have a data table for which a number of records only show null values. I've unticked the "Include Null Values" option and typically this would resolve my issue and hide all records with null values. However, on this occasion, nothing happened and all records remained visible.
The next thing that crossed my mind was that maybe some of the values weren't actually null. I, therefore, created a copy of the table in question and added the ISNULL() function to all of my expressions. This confirmed for me that all of the cells that I thought to be null were indeed null.
In an attempt to get around this issue I found a suggestion from the community to create the dimension as a master item using the formula: =IF(AGGR(#EXPRESSION#, #DIMENSION#), #DIMENSION#, null()). I'm not sure why this worked, but it did and all of the null value records disappeared.
This being said, it caused another issue. My initial intent was to use the dimension in question as part of a drill-down functionality. However, as I'm sure you'll be aware, when using an expression in a drill-down function there is no way of being able to create a dynamic label. Therefore, when I drill down to the level where this expression is used, the column name becomes "=IF(AGGR(#EXPRESSION#, #DIMENSION#), #DIMENSION#, null())" ie the full expression value.
I, therefore, have two questions:
a) In the first instance, why do the records not disappear when all values are null?
b) Is there any way around the issues being experienced with the drill-down functionality?
As a final point, I should note that each of the cells that are appearing null use some complex set analysis logic such that I can't find a way to get them working with a simple IF statement nor can I easily roll them back to be defined within the script.
Sadly, I'm unable to share the data as it's business sensitive. However, I can share a simple screen grab with elements redacted:
The top table shows the result of my expressions in their original form. For the second table, I have enclosed all of my expressions in an isnull() statement to confirm whether or not cell values are null. As you can see record E is null in all cells. In such a case, given that the "Include null values" checkbox has been deselected, I'd expect for record E to have disappeared in the original table, but alas it hasn't. On the other hand, as all other records have at least one cell populated I'd expect them to remain.
An example of the expression I'm using for the "current stock" column is as follows.
I think that there is something wrong with one of the calc.
I loaded this:
Data: load * inline [ SiteCode, %CompletionYesNo, Build Status Code, StockKey A, Y,F,1 B,N,G,1 C,Y,F,1 D,N,F,1 E,Y,G,1 ];
And created pivot:
Top columns here are the calculation so there is no way to get null(). Count on null field is going to give 0 and that is expected behavior. Can you share your app without the data? I think there is something wrong with some of the pivot calcs and would like to understand it.