Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'am facing an issue about formatting a lenght time. I have a difference (Var N / N-1 in the screenshot below) between two lenght. For positive variations, it works (see the first three rows). But in the case of a negative variation, i have ##### and the corresponding number is -0,000365416990416991.
I get the correct value if i multiply this number with Nb seconds in a day (86400).
Do you have an idea what goes wrong??
Thanks
Would you use Fab(Expression) and then check
Meantime, Can you send aplication to work
are you using timeformat and number format with the expression.
No, in my formula i don't use timeformat or number format. I just calculate average with sum(Lengt time) / sum (Case Lenght time).
But in properties/Numbers, i have Interval as Number Format Settings:
Well Time cannot be negative. Probably do something like Anil has suggested. Fabs(YourEquation)
Sorry, i forgot to specify that the problem appears only in Excel export!! In my .qvw it works.
Probably hard code the equation format
Interval( Fabs(YourEquation), 'D mm:ss' )
Per default excel couldn't handle negative times (there is a global setting within the options to change that but it will change also each other dates - therefore it's not recommended to do it). This meant you need some kind of workaround and returning this field as a string-field or you used the above mentioned fabs() approach and adds a further column which showed the "+" or "-" sign (this approach makes it easier if you need to calculate further with these data although you could of course convert a negative time-string into real values, calculate with them and turn it in a string again).
- Marcus
Thanks MArcus for your answer. I have tested the solution with user fabs() and '-' ahead but it don't works. Do you consider i'am obliged to put the negative sign '-' in a column apart.
My formula:
if($(I_Lenght_N) - $(I_Lenght_N_1)<0,'-','') & fabs($(I_Lenght_N) - $(I_Lenght_N_1))
I meant to display it in an additionally column but maybe it's enough to add further spaces to the "-" sign to bypass the excel-interpretation, maybe in this way:
if($(I_Lenght_N) - $(I_Lenght_N_1)<0,' - ','') & fabs($(I_Lenght_N) - $(I_Lenght_N_1))
- Marcus