6 Replies Latest reply on Mar 23, 2018 4:44 PM by Ivan Young

# Second Max Date (if another condition is met) using LOD

Hello everyone,

I have a data set that includes both actuals (for past months) and forecasts (for future months).  There is a column in my data titled "Actuals Indicator" that notes if the given row contains actuals or forecast.

I would like to identify the most recent actuals and the second most recent actuals.  Based on my experiences, I think an LOD formula is the best approach.

So far, I have created the following calculated field to find the most recent actuals (and it seems to be working):

{INCLUDE [Actuals Indicator] : MAX (IF[Actuals Indicator] = "Actuals" THEN [Date] END )}

I used INCLUDE rather than FIXED because I wanted to prevent "NULL" from being one of the results.

But I am having trouble creating a similar calculated field to find the second most recent actuals. Any ideas?

I have attached the sample workbook and a simplified data set to help describe the actuals/ forecasts aspect of this question.

Thanks,

Zach

• ###### 1. Re: Second Max Date (if another condition is met) using LOD

Hi,

Try this,

IF RANK(MAX(IF [Actuals Indicator]='Actuals' THEN [Date]

ELSE

NULL

END),'desc')<3 THEN

'TRUE'

ELSE

'FALSE'

END

drag this to filter shelf and Select "TRUE"

• ###### 2. Re: Second Max Date (if another condition is met) using LOD

Hi Sunil,

Thanks for the quick response.

The "second most recent actuals" needs to be distinguished from the "most recent actuals".

And if possible, I would like to find a solution that does not require a filter.

Thanks,

Zach

• ###### 3. Re: Second Max Date (if another condition is met) using LOD

Hi,

Try this:

{INCLUDE  [Actuals Indicator]:MAX(

IF [Actuals Indicator]='Actuals'

AND [Date] < {INCLUDE  [Actuals Indicator]:MAX(IF [Actuals Indicator]='Actuals' THEN

[Date] END)} THEN Date END

)}

1 of 1 people found this helpful
• ###### 4. Re: Second Max Date (if another condition is met) using LOD

I think { INCLUDE  [Actuals Indicator] : MAX(IF [Actuals Indicator] = 'actuals' AND [Date] <> [Most Recent Actuals] THEN [Date] END) } should work

1 of 1 people found this helpful
• ###### 5. Re: Second Max Date (if another condition is met) using LOD

Thanks for the responses everyone.

Is one of the proposed solutions better than the other?

{INCLUDE [Actuals Indicator]: MAX(IF[Actuals Indicator]= "Actuals" AND [Date] < {INCLUDE [Actuals Indicator]: MAX(IF[Actuals Indicator]= "Actuals" THEN [Date] END)} THEN [Date] END)}

or

{INCLDUE [Actuals Indicator]: MAX(IF[Actuals Indicator]= "Actuals" AND [Date] <> [Most Recent Actuals] THEN [Date] END)}

- - - -

Since it seems there are multiple ways to get the desired outcome, is the following formula the best way to find the latest date of actuals of the previous year? (ie. Max Date within the Second Max of Years that contains Actuals)

{INCLUDE [ Actuals Indicator]: MAX(IF[Actuals Indicator]= "Actuals" AND YEAR[Date] = YEAR [Most Recent Actuals] - 1 THEN [Date] END)}

Thank you!!!

• ###### 6. Re: Second Max Date (if another condition is met) using LOD

I'd use Sunil's, it's all in one.  You don't need to reference another calculation.

1 of 1 people found this helpful