Forum Discussion
Date - Date (new column) dealing with minus numbers
Hi,
I've got 2 date columns and have created a new column which calculates actual start - scheduled start. I have a handful of rows which have returned negative numbers because the user inputted the dates into the wrong column in the system (i've checked front end).
Without resolving the issue in the database and re-extracting the data, how can I resolve these - numbers? When I create my visualisation the minus numbers will be factored when I calculate average time waiting.
Scheduled Start Actual Start Days Waiting(Actual-Scheduled)
05/04/2022 01/05/2022 -4
I am sure it is more simple than I am thinking.
Thanks
HI Anonymous ,
You can write a measure excluding anything below 0 and use the measure for the visual.
e.g. CALCULATE(AVERAGE(Days Waiting(Actual-Scheduled), AVERAGE(Days Waiting(Actual-Scheduled)>=0)
Regards
KT
Assuming the real values can never be negative (i.e. Actual start will always be later than Scheduled Start), then
Use the ABS function to calculate the Absolute value of the difference. Something like" ABS([Actual Start] - [Scheduled Start]) "
3 Replies
ronrsnfldSuper User
Assuming the real values can never be negative (i.e. Actual start will always be later than Scheduled Start), then
Use the ABS function to calculate the Absolute value of the difference. Something like" ABS([Actual Start] - [Scheduled Start]) "
- KT_Bsmart2gethe
Impactful Individual
HI Anonymous ,
You can write a measure excluding anything below 0 and use the measure for the visual.
e.g. CALCULATE(AVERAGE(Days Waiting(Actual-Scheduled), AVERAGE(Days Waiting(Actual-Scheduled)>=0)
Regards
KT
Hi all,
Thanks for your suggestions, unfortunately not dealing with the - numbers isn't an option as it could skew the figures(to exclude anything below 0), I will try to get the data resolved in the database or use the absolute difference. Reading other possible solutions has helped me come to this conclusion so thanks all.