1 Reply Latest reply on Sep 7, 2017 11:03 AM by patrick.byrne.0

    FINDING THE NUMBER OF RECORDS OF ONE MEASURE MEETING A THRESHOLD BASED ON ANOTHER MEASURE

    Aaron Freed

      generally, i want to be able to count how many times some MEASURE 1 (at the row level) exceeds some MEASURE 2 (at the table level).

       

      my specific instance is this:

       

      i have a database of events. each row of the dataset represents a unique EVENT REF. each event has a START DATE and an END DATE. if all events were only 1 day long, it would be a simple matter of using COUNTD(EVENT REF) to say how many events are occuring each day. however, when there are mutiple-day events (i.e. where START DATE <> END DATE), then those events will occur on days that a COUNTD(EVENT REF) would miss (because they are running on a day that isn't associated with a START DATE). in effect, they 'spillover'.

       

      so, what i want to do is to look at each START DATE (at the row level) and find how many events (records) have both an earlier START DATE as well as a later END DATE...to find how many events are 'already in progress' on that START DATE as opposed to starting on that START DATE. in other words, i need to find:

       

      COUNTD(EVENT REF)* where (THIS START DATE >  ANY START DATE) AND (THIS START DATE =< ANY END DATE).

       

      *this could just as well be SUM(NUMBER OF RECORDS)

       

      to warm up, i started with a basic case of SUM(INT(START DATE > #AN EXPLCIT DATE#), but i can only get it to work for, well, an explicit date (e.g. 1 NOV 2017). but instead of an explicit date, i need it to work for the START DATE at the row level.

       

      here is a screenshot of the basic setup (x.date is just a copy of START DATE which i may attempt to replace with a calendar scaffold down the road):

       

      Screenshot 2017-08-25 19.04.12.png

       

      a packaged workbook using tabluau 10.3.1 is attached, as well.