Hi community,
I am not very experienced with Tableau and this is my first question here, because I am completely unable to find an fitting answer online I can reproduce.
How can I count the amount of rows of a Dimension or Measure with missing values?
What is the easiest way without preparing extra fields in the data source or in a secondary data source?
I need a formula or a way directly within Tableau to determine a degree of filling for lots of fields in lots of data sources i.e.
Dimension "street".
How many rows in my data source (often a simple excel file) are filled with a streetname - this is easy and makes no problem
But: how many rows for street are empty?
The same with measures
Measure "sales"
How many rows in my data source have a sales value - ok again - this is easy
But how many rows for "sales" are empty?
I have seen different examples for example with ISNULL, and a second data source with a Boolean Matrix to count this values.
Or a site which explains to set first the values to 0 with NULLS to ZERO.
I can't reproduce this with NULLS to ZERO
Most likely because NULLS-TO-ZERO is a little bit beyond my Tableau skills right now ;-((
Another reason is it is important to NOT set this values to 0 and count the Zeros because I need to distinguish between
the value "0" and the value "BLANK".
Maybe it is possible to make right calculations with the (NULL to ZERO)-way but right now I am not good enough in Tableau to do this.
Really, is there no easier way to do simple kinds of subtractions with Tableau????
Something like
(Number of Values) minus (Number of Values with a "streetname") = (Number of Values without a "streetname")
or
(Number of Values) minus (Number of Values with a "revenue") =(Number of Values without a "revenue")
and so on?
Help would really be very appreciated.
Greetings from Germany
P.Staschik
Pamela,
I"m attaching a workbook that has one method to solve the problem.
I created a calculated field that subtracts the count of a dimension (skips nulls) from the sum of the number of records (includes everything).
You can create separate calculations for each dimension where you need a count of nulls.
FYI - I had to completely remove the blank rows from the bottom of the spreadsheet. Those rows resulted in an incorrect total number of rows.
Hope that helps,
Jeff