5 Replies Latest reply on Aug 22, 2018 4:40 AM by mohana gowthaman

    Using Calculated Field for Partition Function

    mohana gowthaman

      Hello there,

       

      I am relatively new to Tableau, and need help on using CalculatedField.

       

      I have a CSV file with the following columns CARD_ID, CARD_STATUS, EFFECTIVE_DATE.

       

      I would like to get the Count of Records, based on Card Status for the latest record for every CARD_ID.

       

      I have done this in SQL earlier, which will look like these..

      SELECT

      CARD_STATUS,

      COUNT(*)

      FROM

      (

      SELECT * FROM

      (

      SELECT

      *,

      ROWNUMBER() OVER (PARTITION BY CARD_ID ORDER BY EFFECTIVE_DATE DESC) AS RNK

      FROM

      table1

      ) InnerSubView

      WHERE RNK = 1

      ) OuterSubView

      GROUP BY CARD_STATUS

       

      Could you please advise how to do this in Tableau, would much appreciate your help.

       

      Sample Data:

      CARD_ID, CARD_STATUS, EFFECTIVE_DATE.

      C1, Active, 01/Jan/18

      C2, Active, 02/Feb/18

      C1, Suspended, 03/Mar/18

       

      Expected Result:

      Active: 1

      Suspended: 1

       

      Thanks,

      Mohana