4 Replies Latest reply on Oct 10, 2018 11:30 PM by Deepak Rai

# Creating a Dynamic DateDIFF by Current Status

Hi Everyone!

I am currently using Tableau 10 formulas to try to calculate the number of days a record has been at a given status. We pull a report on this information weekly so the table I am creating looks like this (organized from left to right by Record Number, Report Date, Current Status, and my attempt at the days at status count):

Currently the formula is just calculating the date difference from the report date above it. With the following formula:

I would instead like it to calculate the number of work days a record has been at it's current status based on that report date. I am thinking something like the following but my Tableau experience is limited so I apologize:

If First () = 0 Then

0

If Current Status (Of the record in the current row) = Current Status (Matching the status from the report just above that row) Then

DateDIFF( day, Lookup(min(report date)), First row that matches the current row's status), lookup(min(report date)), 0)

Else

0

END

Hopefully that makes sense. Any help is much appreciated!

Edit: Attached is an example of the current problem

• ###### 1. Re: Creating a Dynamic DateDIFF by Current Status

We need a workbook to help you. May be with fake data.

Thanks

Deepak

• ###### 2. Re: Creating a Dynamic DateDIFF by Current Status

I've amended the post. Thanks.

• ###### 3. Re: Creating a Dynamic DateDIFF by Current Status

Hi Lidia

Hope this helps.  Bit complicated table calc is needed.

step 1

step 2

step 3

Thanks,

Shin

• ###### 4. Re: Creating a Dynamic DateDIFF by Current Status

Here It is:

Please Go Through each Calculation as This was Really Tricky to do.

Here is Full Detail

and Here is your final View

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT to Close Thread