Hi,
Requirement: to display the number of taxis which travel time is less than or equal to 40 mins. One each taxi level, if travel time is null, display this measure as null rather than zero.
I tried put following in the measure physical mapping, and default aggregation rule is COUNT(DISTINCT)
CASE
WHEN travel_time <=40 THEN taxi_id
ELSE null
END
Everything works fine but the result returns 0 if travel time is null as count will never return null.
After investigation, we are using following physical mapping in logical column for measure No. of taxis which have travel time less than 40 mins.
--40 mins is the KPI. If travel time <= 40mins, then count the taxi ID and display the result.
cast(
case
WHEN
travel_time <= 40 and count(distinct Taxi_ID) > 0
THEN count(distinct Taxi_ID) )
--If travel time is greater than 40 mins (not meeting KPI), the result should be 0
WHEN
travel_time > 40
and count(distinct Taxi_ID) > 0
THEN 0
--all other scenarios evaluate to null
ELSE NULL
END as DOUBLE PRECISION )
/
nullif( cast(count(distinct Taxi_ID) as DOUBLE PRECISION ) , 0) * 100
After applying logic in the RPD, the RPD added BY[] at the end of each innder CASE END statement. Physical SQL has grouping sets clause. On each taxi level, this measure could be 100 for those taxi's travel time less than 40 minutes. I am expecting 1 or 0 or null on each taxi level.
Any suggestion to correct my logic.
Thanks
