Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sergio0592
Specialist III
Specialist III

Issue with lengt time format

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).

Sans titre.jpg

Do you have an idea what goes wrong??

Thanks

12 Replies
Anil_Babu_Samineni

Would you use Fab(Expression) and then check

Meantime, Can you send aplication to work

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rupamjyotidas
Specialist
Specialist

are you using timeformat and number format with the expression.

sergio0592
Specialist III
Specialist III
Author

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:

rupamjyotidas
Specialist
Specialist

Well Time cannot be negative. Probably do something like Anil has suggested. Fabs(YourEquation)

sergio0592
Specialist III
Specialist III
Author

Sorry, i forgot to specify that the problem appears only in Excel export!!  In my .qvw it works.

rupamjyotidas
Specialist
Specialist

Probably hard code the equation format

Interval( Fabs(YourEquation), 'D mm:ss' )

marcus_sommer

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

sergio0592
Specialist III
Specialist III
Author

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))

marcus_sommer

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