1 Reply Latest reply on Aug 2, 2016 8:36 AM by Stephen Lavery

    Using a date field to present today()-1 date for a measure on a column instead of filter

    Riz H

      Using a date field to present today()-1 date for a measure values on a column instead of filter. Cannot use filtering due to other fields populating data for the continuous time period.

      I have daily accumulating sum which gets rolled up on a daily basis. A calculated column is required which looks at the previous day and populates the sum of the measure field accordingly. I have tried several examples (as below) to no real avail. Would appreciate some insight into how best to do this. I have tried others but am drawing 0's. I have verified the records exists when using the  [Participants] field directly

       

      Example 1:

      CASE DATE([Date])

      WHEN TODAY()-1

      THEN [Participants]

      ELSE 0

      END

       

      Example 2:

       

      CASE TODAY()

      WHEN [Participants_date -1]

      THEN [Participants]

      ELSE 0

      END

       

      Example 3:

      IF attr(Participants]) = ATTR(INT([Participants -1]))

      THEN sum([Participants])

      ELSE 0

      END