4 Replies Latest reply on Jul 7, 2017 10:50 AM by Kendra Allenspach

# Return Max(Date) based on value of another measure

Hi there Tableau gurus,

I am trying to return the max(date) in a data set, but based on another measure.

For example, if the below is my data set, I want the Max Date where Impressions are greater than zero, so 1/1/17, as opposed to the max date in the data set which is 1/2/17.

DateClicksImpressions
01-01-1725
01-02-1740

I tried to do a formula as follows, but it is still returning the max(Date) in the data set.

iif([Impressions]>0,MAX([Date]),null)

I am probably missing something obvious, but as I am still fairly new to Tableau I am stumped. Any help is greatly appreciated!!

Thank you!

Kendra

• ###### 1. Re: Return Max(Date) based on value of another measure

Does this Works?

Thanks

Deepak

1 of 1 people found this helpful
• ###### 2. Re: Return Max(Date) based on value of another measure

Hi Kendra,

Create a calculated field with:

WINDOW_MAX(ATTR(IF [Impressions]>0 then [Date] else NULL END))

It will display the Max Date based on Impressions.

Thanks,

Hari

3 of 3 people found this helpful
• ###### 3. Re: Return Max(Date) based on value of another measure

Thanks Deepak! It's in the right direction, but I am looking for the max(date), so the most recent date in the data set where impressions are >0, not necessarily the date where the impressions are the greatest. Does that make sense? So in the sample data you show, the value I would want returned is actually 1/6/17 - since it is the most recent date that impressions occurred.

1 of 1 people found this helpful
• ###### 4. Re: Return Max(Date) based on value of another measure

Thank you both for your help!

@Hari - your solution worked for the sample data set, but when I tried to apply to my larger data set it wasn't returning the correct result.

However...- both of your posts helped me think about my calc a bit differently and I discovered a solution. The below solution returns the last date where impressions are greater than zero in my data set, across a variety of dimensions (which I think was the extra level of complexity that was throwing off Hari's original solution). I just had to move the "max" outside of the IF expression.

max(IF [Impressions]>0 then [Date] else NULL END)

THANKS for the quick responses!