3 Replies Latest reply: Sep 27, 2015 12:00 PM by Edwin Witvoet RSS

    Find Prev Quarter (Pick in Time)

    Edwin Witvoet

      I have a data table with Metrics and Value per Network.

      Each Quarter is labeled with a QuarterID, inspired by the Pick In Time post here (The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync)

       

      Somehow I don't get it to work, meaning : per row displaying a specific Quarters value and Previous Quarter's value

       

      FindMaxPrevQuarter.png

       

      My current expressions are

      • Prev Q  : Max(QuarterID) - 1
      • Max Q : Max(ds_value)
      • Max 15-Q3 : max({<YY_Q={'15-Q3'}>} ds_value)
      • Max 15-Q2 : max({<YY_Q={'15-Q2'}>} ds_value)
      • Max (Prev Q) : max({$<QuarterID = {$(=Max(QuarterID) - 1)}>} ds_value)
      • Max (Prev Q)(1) : Max(aggr(max({$<QuarterID = {$(=Max(QuarterID) - 1)} >} ds_value), ds_value))

       

      What are the appropriate expressions for Max 15-Q3, Max 15-Q2, Max (Prev Q) to get values in all the Cells?