5 Replies Latest reply on Aug 21, 2018 10:04 AM by Andrew Sielen

    Using a window function as a filter on aggregated data

    Andrew Sielen

      I have a data source that is daily status updates in this format:

       

      Date
      AccountSegment
      Status
      Value
      Daily time stampAccount IDTextText (Red, Green)Number

       

      Each row in the data source is a timestamped status and value update for the Account.

       

      What I am trying to do is build a day over day line chart filtered by the status at the start of the window time period segmented by Segment.

       

      Screen Shot 2018-08-16 at 10.05.38 AM.png

      Two lines for two segments

       

      I have a formula field to calculate %Green out of total over time. However, I want to filter it so only accounts that started as Red are included so it would be [%Green Accounts]/[# of Accounts that started as Red]

       

      I figured out I could see if an account started as Red with a window function ( =LOOKUP(ATTR([Status]),FIRST()) however, the window function doesn't work if I am aggregating it up by Segment. Is there a way to use that in a filter when the data is aggregated? Or a different way to get the first status per account without a window function?