Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the trip duration columns in minutes which calculate the duration time between end_time and starttime columns using interval(end_time-start_time,'hh:mm:ss') function, the negative values effects the visulization by not showing the exact AVG time.
How to calculate it in the right format without negative value??
I hope you have all those columns created in load Script.
If you want to remove the negative values so that they cant be used, try this:-
Sum({<TripDuration={">=0"}>}TripDuration)
If you want to change those neagtives to positive:-
in your load Script add.
TableA:-
Load *, If(TripDuration<0,-TripDuration,TripDuration) as TD from Source;
After that In set expression you can do Sum(TD)
Or if you dont want to change in script try this in set expression:-
Sum(aggr(If(TripDuration<0,-TripDuration,TripDuration),TripDuration))