Skip to Content
0

Convert numbers to sum in Webi

Nov 22, 2017 at 05:01 PM

43

avatar image

Hi All,

I have a requirement to show the sum of numbers in the form of time.

Eg:

Sum should be 7 but it is showing as 6.20. Can someone please suggest on this?

Thanks

capture.png (9.1 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
AMIT KUMAR
Nov 22, 2017 at 06:03 PM
0

use this in the total row.

First i am converting number to string data type to exract hour and mintues in seprate.After that i have converted mintues to hour and added.

=Sum(ToNumber(Substr(FormatNumber([TIME];"0.00");1;Pos(FormatNumber([TIME];"0.00");".")-1))+ToNumber(Right(FormatNumber([TIME];"0.00");2);"00")/60)

Show 3 Share
10 |10000 characters needed characters left characters exceeded

HI Amit,

Thanks for the detailes formula.

This formula worked fine, but while I was testing I found few discrepency for few.. Please find below one set of data.

Output from Bex:

bex.png

Webi output:

webi.png

Can you please let me know what I can correct?

bex.png (1.4 kB)
webi.png (1.3 kB)
0

follow these steps.

Var1=Sum(ToNumber(Substr(FormatNumber([TIME];"0.00");1;Pos(FormatNumber([TIME];"0.00");".")-1))+ToNumber(Right(FormatNumber([TIME];"0.00");2);"00")/60)

Final=ToNumber(Substr(FormatNumber([Var1];"0.00");1;Pos(FormatNumber([Var1];"0.00");".")-1))+ToNumber(Right(FormatNumber([Var1];"0.00");2);"00")/10000*60

Drag this in the total row.

0

Thank you so much Amit. Your solution worked for me.

0
AMIT KUMAR
Nov 22, 2017 at 05:05 PM
0

Based on the screenshot total of all entries is 6.2.How are you expecting it should be 7?

time column is based on variable or direct object? in case of variable then use foreach () in sum row and add dimensions which needs to be there.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

If you consider 2.30 as 2hs and 30mins, then we can get the overall sum as 7. Can we get this?

0