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

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

    Zach DeMascole

      Hello everyone,

       

      Thank you in advance for any help you can offer.

       

      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