topic Re: Issue with lengt time format in QlikView

Issue with lengt time format

sergio0592 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Issue with lengt time format

Anil_Babu_Samineni — Wed, 30 Nov 2016 16:24:32 GMT

Would you use Fab(Expression) and then check

Meantime, Can you send aplication to work

Re: Issue with lengt time format

rupamjyotidas — Wed, 30 Nov 2016 16:51:24 GMT

are you using timeformat and number format with the expression.

Re: Issue with lengt time format

sergio0592 — Wed, 30 Nov 2016 18:13:23 GMT

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:

Re: Issue with lengt time format

rupamjyotidas — Wed, 30 Nov 2016 18:34:16 GMT

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

Re: Issue with lengt time format

sergio0592 — Wed, 30 Nov 2016 21:13:56 GMT

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

Re: Issue with lengt time format

rupamjyotidas — Wed, 30 Nov 2016 21:20:38 GMT

Probably hard code the equation format

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

Re: Issue with lengt time format

marcus_sommer — Thu, 01 Dec 2016 07:10:41 GMT

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

Re: Issue with lengt time format

sergio0592 — Thu, 01 Dec 2016 08:24:55 GMT

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

Re: Issue with lengt time format

marcus_sommer — Thu, 01 Dec 2016 08:41:13 GMT

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

Re: Issue with lengt time format

sergio0592 — Thu, 01 Dec 2016 13:07:01 GMT

No more success with space ' - '

Re: Issue with lengt time format

marcus_sommer — Thu, 01 Dec 2016 14:10:46 GMT

You could try it with another white space chars: Whitespace character - Wikipedia maybe this one: Zero-width space - Wikipedia.

- Marcus

Re: Issue with lengt time format

sergio0592 — Thu, 01 Dec 2016 19:59:08 GMT

Don't works either with ASCII 32. At home i have Open Office and i don't meet the problem but i'am on a sample dummy app, not on my real app but on the same kind of data.