6 Replies Latest reply on Oct 8, 2018 4:53 AM by Zhouyi Zhang

# Finding next date w/o table calc

Hello everybody,

I have a table on price changes for each product which looks basically like this:

I need to know the start and end date of each price for each product.

So I'm looking for a calculation (or join?) to make the [created at]-Date of the next Price Change ID to be the [Left at]-Date of the named ID. Just like that:

And to make it a bit more interesting: I cannot use any table calcs as I need to go on joining and calculating based on those two dates,

I have tried some LOD calcs and joining - both in Tableau Desktop and Prep, but I wasn't able to find a solution.

Does any of you have an idea?

Thanks in advance and have a nice weekend!

Anika

• ###### 1. Re: Finding next date w/o table calc

Anika,

I did it using LOD.

For min date, I used

{ FIXED [Product ID]: MIN([Created at])}

For Price Change Date:

{ FIXED [Product ID]: MIN(if [Created at]<>[Min Date] then [Created at] END)}

• ###### 2. Re: Finding next date w/o table calc

Hi Maneesh,

thanks for you quick answer. Unfortunately you way works only for the first Price Change ID of each Product ID, but I need this for each price change.

I would have to go on and create a field for the 2nd Min date, the 3rd Min date.... and so on but one product can have hundreds of price changes.

Best

Anika

• ###### 3. Re: Finding next date w/o table calc

Hi, Anika

Please find my solution attached by join your data to itself using key product id = product id as shown below

Create two calculation fields as filter

Hope this helps

ZZ

2 of 2 people found this helpful
• ###### 4. Re: Finding next date w/o table calc

Hi ZZ,

thanks for your answer. I will have to check but it looks as if it would work.

Just one question: How exactly do you create the [datediff] calc? Is it DATEDIFF('day', [Created at], [Created at (Sheet11)])?

Best

Anika

• ###### 5. Re: Finding next date w/o table calc

Just answered the question by myself. Yes, it is

DATEDIFF('day', [Created at], [Created at (Sheet11)])

Thanks for your help!

Have a nice day!

• ###### 6. Re: Finding next date w/o table calc

glad you figure it out yourself and sorry forgot to mention the datediff calculation.

ZZ