Using Variables for calculation

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

Isn’t here anybody who can help?
KR Paul

Hi Paul,
as you want to have a seperate result per line item there is on need for the aggregation. So delete the median everywhere, than it should work.

Works?

Br
Daniel