12 Replies Latest reply on Oct 17, 2018 4:38 AM by Tushar More

# issue min, max of date ->results are wrong

Hi everyone.

I'm trying to create a view like in this table below.

But I don't know how to splitt min and max to get just one row of information.

Product
Month
Amount_max
Date_max
Amount_min
Date_min
ABCOctober3811.10.2018 14:574602.10.2018 11:49

I'm struggling in Tableau with min and max function because e.g. if I use max(Date) and max(Amount) then these results

doesn't match. Because on one hand the latest date is shown but the information of the amount column is wrong

if you are using max(Amount) too.

I only need the newest entry and the oldest one per month.

In my example workbook there is just one month and one product. In the real system there are several hundreds of products.

• ###### 1. Re: issue min, max of date ->results are wrong

Hi Patrick,

Use the fixed lod and do this

Thanks

sankar

1 of 1 people found this helpful
• ###### 2. Re: issue min, max of date ->results are wrong

Hi Patrick,

If I understand you correctly you're looking for the min and max dates, and the amount that corresponds with either of these for each month and product?

If you use the min and max of the amount you'll get the amount that in the mix/max during that period, rather than the first or last.

To find these you can used a fixed calculation to match the earliest or latest date for each product and month and find the corresponding amount.

This is the formula you'll need for min, for max just change the max in the fixed formula.

IF  [Date1] =

{fixed [Month1],[Product1] : min([Date1])}

THEN

[Amount]

END

1 of 1 people found this helpful
• ###### 3. Re: issue min, max of date ->results are wrong

Patrick,

Please find my approach stored in the attached workbook.

~Tushar

• ###### 4. Re: issue min, max of date ->results are wrong

Thanks for this tip.

• ###### 5. Re: issue min, max of date ->results are wrong

Thanks it's working.

• ###### 6. Re: issue min, max of date ->results are wrong

How I can use the field max Amt and Min amt if I want to do another calculation? I tried to separate both but it didn't work in this case.

• ###### 7. Re: issue min, max of date ->results are wrong

There is still a problem: If one month only has one entry then max and min are the same.

• ###### 8. Re: issue min, max of date ->results are wrong

Hi Patrick,

What type of calculation are you looking to do? Are you trying to calculate based on the min/max? Depending on the type you should be able to use these fields.

If there is only one record then the min and max will be both the same, what would you be expecting to see?

• ###### 9. Re: issue min, max of date ->results are wrong

That's obvious. What do you want to get there?

• ###### 10. Re: issue min, max of date ->results are wrong

We have a warehouse journal with products (I updated the example workbook, see attached workbook).

In this journal there are only date and time when the stock value/amount has changed like in this table below.

ProductAmount
Date
ABC4011.10.2018  11:10:59
ABC3911.10.2018  14:57:10
ABC3811.10.2018  14:57:54
XYZ215.05.2018  08:37:11
XYZ1003.08.2018  18:47:51
XYZ520.08.2018  09:42:43
XYZ3005.10.2018  10:40:51

Now I want to know what's the value at the beginning and at the end of each month per each product.

Later I also need a filter where I can select months.

Because I nee two fields of amount in order to compare how was the amount at the beginning and at the end of the month.

In this example "XYZ" has in June and July no moving so it will still have the latest vaule of May which is 2.

On Aug 1st "XYZ" still has  the amount = 2 (value of the previous month), on Aug 3 the value has changed and on Aug 20 it has changed the last time for August ->the last day of August=5 etc.

This table below shows what I try to get from tableau

Product
Month
Begin
End
ABCOctober40

38

XYZMay22
June22
July22
August25
September55
October530