0

The calculated column I am trying to create is the far right column in the below table:

In words: If a B event occurs in the specified City, does a C event occur within one hour of the B event, true or false.

I've tried a few different approaches with Over functions but just not having luck. I am struggling on how to reference a different event in the same column and then calculate a datetime difference between them..

olu

Thanks!

jmeddy
  • 109
  • 1
  • 5
  • 16

1 Answers1

0

You may try to take it in stages and define intermediate columns. Try this, assuming C is the max label for the events.

You did not post data so I generated something that seemed similar.

([event]="B") and (Max([event]) OVER (Intersect([city],AllNext([date])))="C") and ( DateDiff("hh",[date],ValueforMax([event],[date]) OVER (Intersect([city],AllNext([date]))))<=1)

Gaia Paolini
  • 1,044
  • 1
  • 5
  • 4
  • This is perfect, thanks so much! Do you know how I would make this work if C was not the max value? I am trying to learn here, trying to do what you provided would've taken me forever. Thanks! I want to get to this level haha – jmeddy Jul 14 '20 at 21:00
  • Also, with the solution you provided, it records a TRUE or FALSE for each row, is there a way to only report for the B events? maybe use a case when then end approach? – jmeddy Jul 14 '20 at 21:10
  • 1
    If C were not the max value, you can go into the column properties of the dataset, locate the [event] column, tab Sort Order and configure a custom sort order so that the new value is last in order. For removing the results should the event not be B, you cannot put a case around the original definition because it then would no longer look at C events. I would create a second calculated column like this CASE [event] when 'B' then [originalflag] else NULL end. Where [originalflag] is the previous solution I posted – Gaia Paolini Jul 15 '20 at 09:17