6 Replies Latest reply: May 30, 2017 8:19 AM by Felip Drechsler RSS

    Set Analysis - last 7 days

    simon bushell

      So I have a heap of transactions each with [CalDate]   - a date formatted as YYYY-MM-DD

       

      I want to count the number of transactions the occurred in the last 7 days ; or be to precise, the last "loaded" 7 days.

      My formula wasn't working, so I've tried to simplify it for testing.
      Now I am just trying to create a set of the desired data, and return the earliest date.

       

      I can't even get it to do that.

       

      Broken down :

       

      • The last date loaded in my dataset, regardless of dimension:
        • Max(Total [CalDate])

      (This works)

       

      • 7 Days previous
        • Date(Max(Total [CalDate])-7)

      (This works)

       

      • Final formula : (should bring back the earliest date present within 7 day dataset)
        • Min( { $< [CalDate] = {">= $(=Date(Max(Total [CalDate])-7))" }>} [CalDate])

      (This just produces NULL)

       

       

       

      QS.PNG