1 Reply Latest reply on Feb 13, 2017 10:50 AM by Alexander Mou

    Selecting values of one column corresponding to values of another

    John Coven

      Hello all,

       

      I have two columns: one corresponds to an event of interest (1 if the event of interest was observed, 0 if otherwise), the other to the time at which the observation was made. Let's call the former "y" and the latter "x" for now.

       

      I need to select the first value of x after which fewer than 5% of the y=1 events occur (or, conversely, the first value of x before which 95% of the y=1 events occur). In other words, I need to be able to identify the time after which relatively few of the events of interest can be expected to occur. Since I need to be able to quickly identify this over a large number of distinct groups, back-of-the-envelope math is not ideal here, but I can't seem to wrap my head around what the Tableau code requirements would look like. Can anyone help?

       

      For reference, I currently have a view set up that has a simple two-column table with the various time stamps as one column and the proportion of remaining y=1s yet to occur as the other (calculated using the calculated field (TOTAL(SUM([Event]))-RUNNING_SUM(SUM([Event])))/(TOTAL(SUM([Event]))) ). The time stamps are ordered from first to last, so the second column has a 1 in the first row and decreases over successive rows to a 0 in the last row. I want to select the first value in the time stamp column where the value in the second is less than or equal to .05. An attached image gives an indication of what I mean.