Apply count() inside of measure

BIAP

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

Thomas Dodds

Do you have a hierarchy of Taxi level -> Taxi?

It's unclear ... if you do, then counting at the higher level I don't expect would ever give you a null ...

I'd restructure the data to answer the question - doing the logic at fetch time (or after) may not give you the desired results.

BIAP

Thanks for looking into this. Thomas.

We don't have a hierarchy for taxi level. I thought the logical dimensions will determine what aggregation level should apply. Is it possible to add over (partition by) in my case statement? RPD re-write my query to include BY [] clause, which is summary level aggregation?