I want to create a chart using a Time value such as 09:45. I have managed to do it by using Time as a decimal (9.75).
When I sync the Time value as Sql Time the phocas database designer converts it as text which comes through as blank when the database builds. I’ve tried to convert the text to time in the database designer but I get errors.
Hi,
I think a lot of BI tools would struggle with this, as time is not a decimal value. I would be looking at making the values minutes or seconds as thats a decimal value not divided by 60. This is where the charting will get unstuck.
Judging by the lack of reponses I think the answer is no I am afraid.
Time is great on a grid so see, but not as a graph unless its a decimal value like seconds or minutes.
Regards,
Jon
Hi @LeeWilson ,
Not sure if this will help you, but I have something similar in one of my databases.
Here’s the column that gets pulled in through the Sync, which is formatted as text:
I then use SQL in a Transform column to convert the time into Seconds, which gets formatted as a Number:
Output:
Finally, I built a Calculated measure that divides: [Transform Column Value] / 60 = Total Mins
Notice that I have the formatting set to Custom to include a comma and 2 decimal places.
Hope this helps!
Tim
Hi!
Thanks so much for the suggestion. Time as a measure (as we call it) is something we are looking into as it comes up fairly often. Right now the plan is to try and implement it smoothly in a few new modes and then see if we can roll it out across the product.
We have to tread carefully as there are a lot of work arounds here and there we don’t want to break so we probably won’t get there fast, but we do want to do something in the area.
Reach out if you want to discuss further.
Sorry for the delay in responding, yeah, I have done something similar, it was just I was asked if it was possible to have the time displayed properly rather than having a decimal.
