Hi all,
I want to calculate the time difference between two activities in an OLAP table. Based on the amount of the time difference I want to use another unit. Is it possible to create a variable which gives you a result based on the row in the OLAP?
For example:
Event From | Event To | Time between
Post Invoice | Pay Invoice | 30 Days
Post Invoice| Cancel Inovice | 2 Hours
This code in the variable will be only calculated once. Therefore the variable unit give me in the second row 2 Days instead of 2 Hours.
CASE
WHEN MEDIAN(DATEDIFF(ss, source(WFMT_EVENTS.TIMESTAMP), target (WFMT_EVENTS.TIMESTAMP))) < 120.0
THEN sec
WHEN MEDIAN(DATEDIFF(ss, source(WFMT_EVENTS.TIMESTAMP), target (WFMT_EVENTS.TIMESTAMP))) < 7200
THEN min
WHEN MEDIAN(DATEDIFF(ss, source(WFMT_EVENTS.TIMESTAMP), target (WFMT_EVENTS.TIMESTAMP))) < 86400
THEN h
ELSE Days
END