Calculated field in tableau to find if the first row is Success or Failure

div Published at Dev

Div

I have the below data (from a table) with fields TransactionDate, UserID, and the StatusDesc. The goal is I want to show the userID, where the UserID does not have a login failure (indicated by statusDesc Column - 'Rejected') occurring prior on the same day as a successful login ( Sucessfull Login is indicated by statusDesc Column - 'Success').

Actual DATA

            TransactionDate                 UserId                      StatusDesc

            2018-12-02 00:00:01.957     [email protected]     Rejected
            2018-12-02 00:00:14.907     [email protected]     Success
            2018-13-02 00:00:22.390     [email protected]     Success
            2018-13-02 00:00:28.610     [email protected]     Rejected
            2018-14-02 00:00:22.390     [email protected]              Success
            2018-14-02 00:00:28.610     [email protected]              Rejected

Expected Result

            TransactionDate                 UserId                      StatusDesc
            2018-13-02 00:00:22.390     [email protected]     Success
            2018-14-02 00:00:22.390     [email protected]              Success

Luke B

You can use an LOD (Level of Detail) expression in a Calculated Field for this. LOD's can be tricky to wrap your head around at first, but once you've used them a few times they become very handy.

Create a new calculated field under Dimensions called "First Rejection of Day". This will have the timestamp of the first rejection of the day for the user, or null if there have been no rejections. Use the following code:

{ FIXED [UserId], DATE([TransactionDate]) : MIN(
    IF [StatusDesc] = "Rejected" THEN
      [TransactionDate]
    END
  )
}

Create another calculated field called "Has Prior Rejection". Use the following code:

ISNULL([First Rejection of Day]) = FALSE AND [First Rejection of Day] <= [TransactionDate]

Drag Has Prior Rejection to Filters, and select "False". This will filter out all records that have a prior rejection.

Screenshot in Tableau

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-12-5

Comments

0 comments

TOP Ranking

Article

Calculated field in tableau to find if the first row is Success or Failure

Calculated field in tableau to find if the first row is Success or Failure

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

pump.io port in URL

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

How to import an asset in swift using Bundle.main.path() in a react-native native module

How to use HttpClient with ANY ssl cert, no matter how "bad" it is

Modbus Python Schneider PM5300

What is the exact difference between “ use_all_dns_ips” and "resolve_canonical_bootstrap_servers_only” in client.dns.lookup options?

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

BigQuery - concatenate ignoring NULL

split column by delimiter and deleting expanded column

Unable to use switch toggle for dark mode in material-ui

Soundcloud API Authentication | NodeWebkit, redirect uri and local file system

Apache rewrite or susbstitute rule for bugzilla HTTP 301 redirect

Is there an option for a Simulink Scope to display the layout in single column?

UWP access denied

Center buttons and brand in Bootstrap

express js can't redirect user

Make a B+ Tree concurrent thread safe

Printing Int array and String array in one

Google Chrome Translate Page Does Not Work

Elasticsearch - How to match number range in string