Forum Discussion
Drop Seconds from Duration Measure
BICrazy ok no worries, however the second script i posted does work.
BICrazy what error are you getting? you can return what you want, setting the variable doesn't mean you have to return it
BICrazy does your code run with you original script?
vanessafvg
If I don't return the seconds, I receive the following error: Failed to resolve name 'CONCATENATE'. It is not a valid table, variable, or function name.
The 2 codes that I posted are created as 2 separate measures and the latter is dependent in the 1st measure.BICrazy are you able to share your file with me?
Convert to Seconds =
VAR Duration =
SUMX ( TrackerDailySummary, [Duration2Seconds] )
VAR Hours =
INT ( Duration / 3600 )
VAR Minutes =
INT ( MOD ( Duration, 3600 ) / 60 )
VAR Seconds =
MOD ( Duration, 60 )
VAR H =
IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) ) // Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
) // Seconds with leading zeros
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)
VAR tH =
IF ( Hours = 0, "", IF ( Hours > 1, Hours & " Hours, ", Hours & " Hour, " ) )
VAR tM =
IF (
Minutes = 0,
"",
IF ( Minutes > 1, Minutes & " Minutes, ", Minutes & " Minute, " )
)
VAR tS =
IF (
Seconds = 0,
"",
IF ( Seconds > 1, Seconds & " Seconds", Seconds & " Second" )
)
RETURN
CONCATENATE ( th, CONCATENATE ( "", CONCATENATE ( tm, CONCATENATE ) ) )Thanks for all your assistance. I initially thought that I was doing something wrong.
I've managed to convert the duration to hours and minutes by removing seconds from the return on the 1st Measure. This drops the overall time but I figured that I'm dropping all the seconds from each individual record in my data which was adding up to minutes.BICrazy ok no worries, however the second script i posted does work.
