6 Replies Latest reply on Oct 24, 2017 10:41 PM by Deepak Rai

# Calculate MIN date with some spicy problems

I have some duration date stuff I am working on, but in some very limited cases there are dates in the data (probably wrong, but we have to work with it for now) that are say step 4 in a process, but which have a date significantly before the first step.

What I am trying to do is tell tableau that if the date for a step not named "step 1" has a date before that of step 1, I want you to use the date from step 1 as the start date, otherwise just use MIN([Start Date])

I am pretty sure this is an LOD calc I am not getting right in my head.

The generic data that should work is

ProjectStepStart DateEnd Date
P113/17/201512/14/2015
P123/17/201511/17/2015
P134/6/20156/25/2015
P146/15/201511/27/2015
P1511/2/201512/14/2015
P2110/31/20147/30/2015
P2210/31/20147/30/2015
P234/16/20141/9/2015
P2410/31/20147/13/2015
P255/4/20157/30/2015
P3111/14/20147/14/2015
P3211/14/20147/14/2015
P339/9/20141/23/2015
P3411/14/20146/26/2015
P355/30/20157/14/2015

You can see where P2 and P3 Step 3 is "before" Step 1 which is not possible.  Given that some of this data is very old with no real ability to fix it easily, we are relegated to making anything that isn't step 1 with a date before step 1 have the same start date as step 1.

appreciate the help on this.

• ###### 1. Re: Calculate MIN date with some spicy problems

Hi Justin,

Pl Check screenshot and attached. The Calcs are in attached.
Thanks

Deepak

• ###### 2. Re: Calculate MIN date with some spicy problems

Here are the Calcs:

Thanks

Deepak

• ###### 3. Re: Calculate MIN date with some spicy problems

Hi, Justin

Please find my solution attached as well as the screen shot

Hope this could help

ZZ

• ###### 4. Re: Calculate MIN date with some spicy problems

Hi Justin

see the below image:

The first column contain the start date as in your data the second column is correction based on the logic you mention ...

I used pan level table calculation to compare the value with the first row in the pan if less it display the first row in pan, else it display the MIN date in pan ... (You can see the calculated field formula in the image)

Hope this fulfill what you looking for, and you find it correct or helpful.

Best Regards

• ###### 5. Re: Calculate MIN date with some spicy problems

@Deepak Rai

Not quite right; you see where step one was 10/31/2014 on P2?  well your formulas are changing step 1's dates to the April date.  That is not correct.  Step 1's date should never change.  It is the reference date.  so in P2 Step 3 the date should change to 10/31/2014 without changing the date for Step 1, step 1 should remain 10/31/2014.

• ###### 6. Re: Calculate MIN date with some spicy problems

You Mean This?

Thanks

Deepak