Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to create a new report based on an existing report, that will show me all records in which the product Name doesn't exist (null). To be clear, If there are 4 rows of missing product Name data in the original report, this report will show only these 4
In this case, what do I need to add to the product name column and to the rest of the columns?
Thanks,
Alon
The easiest way to achieve this is to remove the other measures and add one new measure - if(isnull([Product Name]),'Missing Product Name'). You would then just need to disable "Include zero values".
The thing is I need to show these columns as well
Then you could either modify those columns with a similar if(isnull()) condition, or you could create a new dimension instead:
If(Isnull([Product Name]),'Missing Product')
You would then place this dimension in your chart and uncheck "Include null values" for the dimension.
didn't work. it shows me no rows, even though there are such cases
Use below as calculated dimension and uncheck suppress zeroes-values / uncheck 'Include zero values' .
Aggr(IF(Len(trim([Product Name]))=0, 'Missing Product Name'),DocNum)
or
IF(Len([Product Name])<=0,'Missing Product Name')
I can confirm that this works, assuming your values are null. I use it regularly.
And with nulls hidden:
If your values are not actually null, you'll have to check for len(Field)=0, or whatever other option you prefer, rather than isnull().
I've tried both formulas:
=if(isnull([Company Name]),'missing company')
//=if(len([Company Name])=0,'missing company')
And it still doesn't work:
I know this script should work. Any idea what the problem might be?
Is the company name actually null? Or is it missing entirely (that is, it's a result of a key to another table and no row exists)? It seems like it might be the latter...
If that's the case, you could force the issue with aggr(), something like:
=if(isnull(aggr(Only(CustomerName),UniqueCustomerID)),'Missing')