5 Replies Latest reply on Jun 16, 2016 2:22 PM by Derrick Austin

# Displaying the Maximum Date for repeating data

Hi all. I am trying to figure out a way to create a logical statement that will scan the code column, and figure out the maximum date for each code (that might have more than one entry), then summarize which row of the repeating fields has the maximum date with a 1 or a 0 in a column next to value (1 being a row with a maximum date for a certain code, 0 being not a maximum date).

In other words, the maximum date for A = 16/11/2012, for B = 10/11/2012, C = 15/11/2012. The rows for the fourth column would look like:

0

0

1

1

0

0

1

Any comments would be highly valued. Thank you!

• ###### 1. Re: Displaying the Maximum Date for repeating data

Hey Joshua,

You can determine the maximum date with an LoD calculation.

{FIXED [Code] : MAX([Date])}

Then, you simply apply an if statement to your current row to determine if it is the MAX or not.

• ###### 2. Re: Displaying the Maximum Date for repeating data

What do you where do you apply this to an If statement? Could you give an example? I am having a similar problem, I am looking for the max date for approved and declined loans (one of each) however once I add the Project, it gives the max date for each project's approved and declined loans (essentially the filter breaks down)

Let me know if you can help!

Thanks,

T.

• ###### 3. Re: Displaying the Maximum Date for repeating data

Hey Theodore,

The LoD calculation allows you to specify the exact granularity you need.

In this case, we were looking for the last day of the project (Designated by [Code]), but it can be adjusted as needed.

Basically, you would just create an if statement like this:

[Date] = {FIXED [Code] : MAX([Date])}

• ###### 4. Re: Displaying the Maximum Date for repeating data

Hey Derrick,

Thanks for the help but that doesn't look like an if statement

I tried IF [Grant Status Date]={FIXED[Grant Status]:MAX([Grant Status Date])} THEN [Grant Project] END

(Or in lines with this example IF [Date]={FIXED[Code]:MAX([Date])} THEN [Value] END)

But this just returned a null values.

Thanks,

T.

• ###### 5. Re: Displaying the Maximum Date for repeating data

Hey Theodore,

It's actually a "short hand" if statement. It returns a true/false value that you can use as a filter.

IF [Grant Status Date]={FIXED[Grant Status]:MAX([Grant Status Date])} THEN [Grant Project] END

Should work though.

I would debug by pulling the fixed piece "{FIXED[Grant Status]:MAX([Grant Status Date])}" into its own calculated field to ensure it is returning what you are expecting.