14 Replies Latest reply on Oct 13, 2017 1:16 AM by Xavier De Coster

# Lowest price for latest day

Hello all, first time poster here.

I'm trying to do what seemed like an easy calculated field, I need, for a given triplet ( [Level1],[Level2],[Level3] ) to find the lowest price of the latest day:

Level 1Level 2Level 3DatePrice
HondaCarRed02/06/201710000
HondaMotorcycleBlue02/06/20175000
BMWCarRed02/06/201715000
BMWCarRed03/06/201712000
BMWCarRed03/06/201711000
BMWCarBlue02/06/20177500

With the above example I would have liked to find the following values (especially the one in orange)

 Honda Car Red 02/06/2017 10000 Honda Motorcycle Blue 02/06/2017 5000 BMW Car Red 03/06/2017 11000 BMW Car Blue 02/06/2017 7500

I was thinking of using the following formula but it isn't working as I was hoping:

{FIXED [Level1],[Level2],[Level3], {FIXED [Level1],[Level2],[Level3]: MAX([Date])}: MIN(Price)}

I'm new to Tableau and I feel like I migh be missing a concept.

Any help is welcome

• ###### 1. Re: Lowest price for latest day

Hi Xavier

couple of concepts issues here - first is the Last Date

When using a Fixed statement Tableau will formu permutations of the dimensions that precede the colon(:) and then aggregate them by what follows the colon

- in your case {FIXED [Level1],[Level2],[Level3] : MAX([Date])}

will return the date you are looking for

Then you need to write and if statement based around the last date

{FIXED [Level1],[Level2],[Level3]:if [date] ={last date from above} then  MIN(Price)  else 0 end}

You may need to play with the format

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Lowest price for latest day

Hi Xavier,

Pl check Screenshot and attached. You need to find first time the latest date and use it as a Dimension to get Lowest Price.

Thanks

Deepak

• ###### 3. Re: Lowest price for latest day

Thank you for your help. I tried to implement your solution but got an error thrown at me.

When creating the calculated field with the following code:

"{FIXED [Level1],[Level2],[Level3]:if [date] ={last date from above} then  MIN(Price)  else 0 end}"

I get the following error:

"Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions"

• ###### 4. Re: Lowest price for latest day

Unfortunately the suggested solution doesn't work on my real data set.

If I understand correctly I need to create a "Latest Date" field for each triplet ( [Level1],[Level2],[Level3] )

But in this case wouldn't this mean that {FIXED [Level1],[Level2],[Level3],[Last date]: MIN([Price])} is ultimately the same as {FIXED [Level1],[Level2],[Level3]: MIN([Price])} ?

• ###### 5. Re: Lowest price for latest day

hi

you need to use both fomulas

the first gives you the last dat - I will call it last date

Then you need to use in in the second formula as

{FIXED [Level1],[Level2],[Level3]:if [date] =MAX(([last date]) then  MIN(Price)  else 0 end}

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 6. Re: Lowest price for latest day

No..it wont be same.because Once Latest Date is in the formula, the data is aggregated to Maximum Date. There is fundamental difference between both. Try using both and see the difference.

• ###### 7. Re: Lowest price for latest day

Hi Jim,

That's what I did:

• ###### 8. Re: Lowest price for latest day

Hi Deepak,

Maybe my example wasn't good, when using the following data you can see I don't have the info I'm looking for:

Level 1

Level 2

Level 3

Date

Price

HondaCarRed02/06/201710000
HondaMotorcycleBlue02/06/20175000
BMWCarRed02/06/20175000
BMWCarRed03/06/201712000
BMWCarRed03/06/201711000
BMWCarBlue02/06/20177500
• ###### 9. Re: Lowest price for latest day

Now, to me it looks that you are looking for a Minimum date first and the corresponding price. My previous calculations were perfect, except that now I found the Minimum Date instead of Maximum.

Hope it helps:

Thanks

Deepak

• ###### 10. Re: Lowest price for latest day

Here is attachment

• ###### 11. Re: Lowest price for latest day

sorry

it is lookin at the first date and saying it needs to aggregate

{FIXED [Level1],[Level2],[Level3]:if MIN([date]) =MAX(([last date]) then  MIN(Price)  else 0 end}

• ###### 12. Re: Lowest price for latest day

Almost! Thank you for pointing me in the right direction

The solution you provided was always sending back 0 unless there was only one date for a given triplet ( [Level1],[Level2],[Level3] )

The query giving me the right value was:

{FIXED [Level1],[Level2],[Level3][date]:if MAX([date]) =MAX(([last date]) then  MIN(Price)  else 0 end}

• ###### 13. Re: Lowest price for latest day

There is only one Date so what is diff between Date & Last Date

• ###### 14. Re: Lowest price for latest day

Sorry I didn't repost it as it was in a previous comment.

Here's the complete solution:

You have to define a first calculated field called "Last Date"
{FIXED [Level1],[Level2],[Level3] : MAX([Date])}

Based on this you can create this field called "Lowest Price on Latest Day Calc"

{FIXED [Level1],[Level2],[Level3][date]:if MAX([date]) =MAX(([last date]) then  MIN(Price)  else 0 end}

The value will either be zero if the date is not the latest date, or the minimum price.

Finally you can create this final field to tidy it up:

{FIXED [Level1],[Level2],[Level3] : MAX([Lowest Price on Latest Day Calc])}

The value will always be the Lowest Price on Latest Day for that particular [Level1],[Level2],[Level3] triplet