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.