2 Replies Latest reply on Mar 26, 2013 7:50 AM by Kristin Chula

    Identify the first time period where measure is NULL

    Kristin Chula



      I have a data set that is a little unusual, where the main measure (Rxs) only exists for up to a certain time period (in this case, quarters), and not for others, which my attached workbook shows.

      My goal is a calculated field that identifies either the Last quarter where Rxs exist, or the First quarter where Rxs are NULL, ideally being able to keep the discrete Period in either the row or columns shelf, or the level of detail..


      For a little more context, I will be using the PREVIOUS_VALUE() function to forecast out Rxs given certain factors (in this example I just had it grow by 10%).  I want to use the requested calculated field to (in addition to other things) make another calculated field, something that will say that when the actual period is <= the last quarter where RXs exist then "RAW", else "FORECAST".  I will add that to the column shelf but then HIDE where = "RAW" to clean up the quarters not part of the forecast.  I can't exclude those periods because then PREVIOUS_VALUE doesn't work, but hiding them would, and I can't hide specific quarters because the underlying data will be changing.


      Any ideas?