1 Reply Latest reply on Aug 10, 2018 12:20 PM by Patrick A Van Der Hyde

    Query - Dashboard building

    Satyen Gotecha

      Hey guys, I am new to Tableau and I have an requirement to build an Data Quality Dashboard. I was wondering I can get some assistance in developing this.

       

      Details:

       

      1st requirement - To show a visual of all fields in the table with their appropriate counts of nulls/valid values/invalid values

       

      Table name:

      Customers


      Fields:

      - Name

      - Address

      - CustomerCategory

      - DOB

      - CustomerType

       

      So I want three checks with counts to be done, Nulls, Valid Values, Invalid Values

       

      The end result I would like is a stacked bar chart (x axis - count/percentage, y axis - field name). Each bar on the chart will represent one field e.g. Name or DOB...so on and will comprise of three counts of the three checks I have mentioned above and will total up to 100%. And it will also have two filters  - activity_month, provider_code

       

      What is the best way in doing this? And if I need to create a calculated fields what's the optimum way?

       

      Option 1

       

      Do I need to create a calculated field for each field for each check?

       

      e.g. Name_null            - Total count null values
             Name_invalid       - Total count invalid values

             Name_valid          - Total count valid values

       

            CustomerCategory      - Total count null values
            CustomerCategory      - Total count invalid values

            CustomerCategory      - Total count valid values

       

      In this option this would create many calculated fields.

       

      Option 2

       

      Do I create three fields with their total respective counts per check for all fields:

       

      e.g. Total_Null
             Total_Invalid
             Total_Valid

       

      I coded this previously in SQL and brought it to Tableau data source and reliased I need row level data which is a requirement for the next visual Requirement 2

       

      SQL example

       

      SELECT

      'CustomerCategory' AS 'field',

      SUM(CASE WHEN CustomerCategory IS NULL THEN 1 ELSE 0 END) AS 'null',

      SUM(CASE WHEN CustomerCategory NOT IN ('01', '1','02','2') THEN 1 ELSE 0 END) AS 'invalid',

      SUM(CASE WHEN CustomerCategory IN ('01', '1','02','2') THEN 1 ELSE 0 END) AS 'valid',

      der_activity_month,

      der_provider_code

      FROM Customers

      GROUP BY

      activity_month,

      provider_code

       

      UNION

       

      --2

      SELECT

      'DOB' AS 'field',

      SUM(CASE WHEN DOBIS NULL THEN 1 ELSE 0 END) AS 'null',

      SUM(CASE WHEN DOB = '1900/01/01' THEN 1 ELSE 0 END) AS 'invalid',

      SUM(CASE WHEN DOB <> '1900/01/01' OR aea_initial_assessment_date IS NOT NULL THEN 1 ELSE 0 END) AS 'valid',

      der_activity_month,

      der_provider_code

      FROM Customers

      GROUP BY

      activity_month,

      provider_code

       

      If I were to go with the second option how do I code to get the Total count of nulls and Total count of Invalid values and Total count of valid values in Tableau?

       

       

      2nd requirement:

       

      Show a dynamic drill down per field visual on a bar chart to show the total count of invalid and null values.

       

      E.g.

       

      CustomerCategory 
      Valid values 01, 1, 02, 2

       

      So on the bar chart I would like to show invalid values which are not in the above valid value list. And also count of nulls

       

      --------------------------------------------------------------------------

       

      Reading the above, I would appreciate if anyone can give me the guidance how to build the dashboard correctly.

       

      Much appreciated and thank you.