8 Replies Latest reply on Jul 22, 2018 8:49 AM by Scott Carman

# Max and Min Date Working Together

Morning all!

Ok so I am working on a project to look at contract information.  In the viz that I am creating I want to look at the amount of days left on a contract before the end date.

The issue that I am having is that the dataset has a contract award number but everything else in the dataset (including the start and end dates) have more than value for each contract award number.  I don't know how to get down to just one start and stop date for a contract award number.

The best results have been achieved when, in my real project I have added MAX(END DT) and MIN(START DT) in a variety of places such a filter, rows, details.  So if all I wanted to give was a representation of an Excel sheet I would be in business!  NOT!

I have tried a LOD expression but seems that I can only fit either MAX(END DT) or MIN(START DT) to that expression.  Additionally, in my real dataset, there is a version number so I have used a LOD expression to pull the MAX version number but the calculation seems to take such a LONG TIME!

I am wondering if any of you have any suggestions on where to go with this data.

Thank you,

Scott

PS - I am working with Tableau 10.5 and have attached a sample workbook with made up data.

• ###### 1. Re: Max and Min Date Working Together

Hi Scott,

You should be able to get the overall start/end dates using LoD calculations.

Example:

I did a similar calc for ContractEnd, using max(End Date).  Then wrote a calc for contract length that subtracts the 2 and was able to get to this.

Is this what you are trying to accomplish?  Hope it helps!

- Jim

1 of 1 people found this helpful
• ###### 2. Re: Max and Min Date Working Together

Jim,

That seems to be the trick!  I just didn't think about the calculation to remove the LOD expression from the contract length.

Can you save the workbook for 10.5 and reattach it as I am not able to open it?

Thanks,

Scott

• ###### 3. Re: Max and Min Date Working Together

Sure thing, Scott.  Here it is.

1 of 1 people found this helpful
• ###### 4. Re: Max and Min Date Working Together

Jim,

This is awesome!  Thank for your input.  I had the LOD expression figured out, I just didn't have the ATTR included in the viz.  That makes it work!

So I recently took the Tableau II:Desktop Fundamentals Course.  I knew that an LOD was the right "neighborhood" that I was looking for but the ATTR I never thought of.  Can you offer up a piece of advice to help settle the confusion in my mind?

Thanks,

Scott

• ###### 5. Re: Max and Min Date Working Together

Hey Scott,

I'm not sure exactly what you mean.  The calcs/viz will still work if you were to remove those fields from the viz.  I displayed them there just to make sure that I was getting the results that I expected.

I often use the ATTR function for this kind of testing, because it's basically a test of uniqueness.  So, when I'm expecting only 1 result, it's a quick and easy test.  If those ATTR displays were *, I'd have known I had something wrong.  But if you remove them, the viz will still work.

What were the LOD calcs that you were trying to use?  That might help me break down why you weren't getting the results you wanted.  I didn't see any LOD calcs in your sample workbook, so it will be helpful to know what you tried.

- Jim

• ###### 6. Re: Max and Min Date Working Together

Jim,

I appreciate your feedback and the guidance on how to use the ATTR as a "sniff test" to see if you are getting the expected results.  I would think this would be on par with a COUNTD function at time when your testing to see if every value is represented.

The LOD expressions I was using, where the same ones you used as well.  What I couldn't decipher was whether the value that I had was correct or not.  I purposefully left them out because I wanted to see the thought process a more experienced individual would use to solve the same problem.  So I am really happy that you came back with the same answer.  The "icing on the cake" is the ATTR to see if the value is correct.

Thanks!

Scott

• ###### 7. Re: Max and Min Date Working Together

Scott,

That makes sense.  I could have also just displayed the discrete values to make sure it was working as expected.  No magic to ATTR in this case, just a habit I've gotten into, I guess.

But glad I could help get it working.  Please remember to close the thread so others can find it if they have the same question.

- Jim

• ###### 8. Re: Max and Min Date Working Together

Jim,

I am still working on the same issue but in a different way.  Can you assist?

How to aggregate a data connection to row level data???

Thanks,

Scott