12 Replies Latest reply on Sep 4, 2016 11:20 PM by willy Anderson

    New tableau player...need help on grouping

    willy Anderson

      Hi All,

       

      let me start with an apologize for my first post to be a question, but this is rather urgent.

       

      I have data with dimension:

      Order date, order id, order item id, etd date, delivered date

       

      The raw data look like this:

       

      Order date

      Order id

      Order item id

      etd date

      delivered date

      10-Jan-16

      A001

      X001

      10-Jan-16

      9-Jan-16

      11-Jan-16

      A001

      X002

      11-Jan-16

      15-Jan-16

      12-Jan-16

      A002

      X003

       

      10-Jan-16

      13-Jan-16

      A003

      X004

      14-Jan-16

       

      14-Jan-16

      A003

      X005

       

       

      15-Jan-16

      A004

      X006

      16-Jan-16

      12-Jan-16

      16-Jan-16

      A005

      X007

      17-Jan-16

      13-Jan-16

      17-Jan-16

      A005

      X008

      18-Jan-16

      14-Jan-16

      18-Jan-16

      A005

      X009

      19-Jan-16

      15-Jan-16

      19-Jan-16

      A006

      X010

      20-Jan-16

      16-Jan-16

      20-Jan-16

      A006

      X011

      21-Jan-16

      17-Jan-16

      20-Jan-16

      A006

      X012

      22-Jan-16

      18-Jan-16

       

      What I want to do are:

       

      - I want  to count number of false data. By false data I mean any data with blank etd date or delivered date. For this I create a calculated field with formula:

      IF ISNULL([Delivered Date])

      then 1

      elseif ISNULL([Etd Date])

      then 1

      else 0

      END

       

      - I want to count number of order that on time. On time mean any order that is not false data and delivered date < etd date. For this I create calculated field with formula:

      IF [false data] = 0 and [Delivered Date] <= [Etd Date] then

      1 else 0

      END

       

      The end result for the "raw" data are like this:

       

      Order date

      Order id

      Order item id

      etd date

      delivered date

      False data

      On time

      10-Jan-16

      A001

      X001

      10-Jan-16

      9-Jan-16

      0

      1

      11-Jan-16

      A001

      X002

      11-Jan-16

      15-Jan-16

      0

      0

      12-Jan-16

      A002

      X003

       

      10-Jan-16

      1

      0

      13-Jan-16

      A003

      X004

      14-Jan-16

       

      1

      0

      14-Jan-16

      A003

      X005

       

       

      1

      0

      15-Jan-16

      A004

      X006

      16-Jan-16

      12-Jan-16

      0

      1

      16-Jan-16

      A005

      X007

      17-Jan-16

      13-Jan-16

      0

      1

      17-Jan-16

      A005

      X008

      18-Jan-16

      14-Jan-16

      0

      1

      18-Jan-16

      A005

      X009

      19-Jan-16

      15-Jan-16

      0

      1

      19-Jan-16

      A006

      X010

      20-Jan-16

      16-Jan-16

      0

      1

      20-Jan-16

      A006

      X011

      21-Jan-16

      17-Jan-16

      0

      1

      20-Jan-16

      A006

      X012

      22-Jan-16

      18-Jan-16

      0

      1

       

       

       

      Now, I want to display:

       

      - How many order id that have false data (in this case it should be 2 (A002 and A003)

      - How many order that all of the order item is on time (in this case it should be 3 (A004 - A006)

      - How many order in total (in this case it should be 6, A001-A006)

       

      An the report is group by month

       

      Final format would look like this:

      Month

      Total order

      Total order false data

      Total perfect delivery

      Percentage perfect order

      Jan

      6

      2

      3

      50%

       

       

       

      The purpose is to count how many order that perfectly delivered to customer. Anyone can help me on this?

       

      Message was edited by: willy Anderson. Reason: remove duplicate table