6 Replies Latest reply on Mar 27, 2018 7:02 AM by Simon G.

# Selecting the value from different rows in a time Series

Hello,

i have sell-price data in the following form:

Artikel      valid from          new price          old price

1               01.05.2017          4,99               3,99

1               06.04.2017          5,99               4,99

1               11.12.2017          2,99               5,99

2               02.03.2017          1,99               0,99

2               17.11.2017          2,99               1,99

2               20.12.2017          3,99               2,99

I want to have the exact price for each day in 2017, so i attached a column with the pure value 1 and inner joined a matrix consisting of:

1     Date

1     01.01.2017

1     02.01.2017

and so on.

Now i want tableau to choose the right price for each day, meaning that for article 1 it should display 4,99 on 05.04.2017 and 5,99 on the next day.

How can i accomplish that?

Any help appreciated!

• ###### 1. Re: Selecting the value from different rows in a time Series

Hi Simon,

Could you please share a sample datasource?

The one you've texted has too few rows.

Besides, if the [valid from] is a date formatted as dd.mm.yyyy,

the first two rows looks ambiguous: the May row appears before the April one

(if the [new price] / [old price] value pairs would be taken into account).

Yours,

Yuri

• ###### 2. Re: Selecting the value from different rows in a time Series

Hello Yuri,

thank your for response. Yes, the first two lines are ambiguous. You can switch the dates around. Unfortunatelly I cannot poste the exact dataset. I can add further examples, but the data remains the same.

Kind regards

Simon

• ###### 3. Re: Selecting the value from different rows in a time Series

Hi Simon,

With the sample data corrected, it is possible to build a view

using FIXED LOD calcs to filter the proper rows in a Cross-Join.

The other fine point is to get the [old price] value for those rows

with the [Date] less than a minimal [valid from] for each [Artikel] --

and the [new price] otherwise.

Hope it could help.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 4. Re: Selecting the value from different rows in a time Series

That seems to work, Thank you!

• ###### 5. Re: Selecting the value from different rows in a time Series

Simon, you're welcome.