Calculations on hours ?

[former_member182541]

Dear All,

I need to do a cell level calculation, if i do that in bex i cannot incorporate in webi based on the limitation. So i am trying to do that in webi level. The problem i am facing is i have a bex output where the calculation is based on hours. Take a look at the output below in bex.

When i take the same report to webi report it displays as same, now i need to sum up the hours here based on the "IDEAL TIME FOR REASONS". How to achieve this in webi. Appreciate your help on this.

[Former Member]

You have to first indentify hour,minutes & seconds from your time difference field using formula like

Hour =if length([time difference) = 7 then  substr([time difference];1;1) else  substr([time difference];1;2)

Minutes =if length([time difference) = 7 then  substr([time difference];3;2) else  substr([time difference];4;2)

Seconds = if length([time difference) = 7 then  substr([time difference];6;2) else  substr([time difference];7;2)

Now all of these would be measure objects

after that just add the hour ,minutes ,seconds by using variable

var_total = [hours]+[minutes]+[seconds]

another variable

var_hours = [var_total]/24

var_minutes = ([var_total] - [var_hours]*24)/60

var_seconds = [var_total] - [var_hours]*24-[var_minutes]*60

And then concate all the three variables which will give you result in hh24:mm:ss format.

Thanks,
Swapnil

[former_member182541]

Just to alert you both my objects are dimensions, when i am doing the hours calculations i am getting multi-value error.

[Former Member]

Please try below formula:

Hour =if length([time difference) = 7 then  sum(substr([time difference];1;1)) else  sum(substr([time difference];1;2))

Minutes =if length([time difference) = 7 then  sum(substr([time difference];3;2)) else  sum(substr([time difference];4;2))

Seconds = if length([time difference) = 7 then  Sum(substr([time difference];6;2)) else  sum(substr([time difference];7;2))

[Former Member]

Have you tried the formula??

You got you answer?

I want to change one step which is highlighted...

You have to first indentify hour,minutes & seconds from your time difference field using formula like

Hour =if length([time difference) = 7 then  substr([time difference];1;1) else  substr([time difference];1;2)

Minutes =if length([time difference) = 7 then  substr([time difference];3;2) else  substr([time difference];4;2)

Seconds = if length([time difference) = 7 then  substr([time difference];6;2) else  substr([time difference];7;2)

Now all of these would be measure objects

after that just add the hour ,minutes ,seconds by using variable

var_total = [hours]*3600+[minutes]*60+[seconds]

another variable

var_hours = [var_total]/24

var_minutes = ([var_total] - [var_hours]*24)/60

var_seconds = [var_total] - [var_hours]*24-[var_minutes]*60

And then concate all the three variables which will give you result in hh24:mm:ss format.

Thanks,
Swapnil

[former_member182541]

I got the first part getting the total of hours, minutes & seconds now i need the total hours, minutes & seconds.

I multiplied the hours with 60 and then add with minutes then i divided with 60, i got the hours, but then i got stuck up in minutes again. Please note that total hours can be more than 24.

[Former Member]

lets say your total is 2790

So for hour

Hour  = 2790/24 = 46.5..... take integer so final value is 46 only

Now to calculate minutes

Minutes = (2790 - Hour*60)/60 = (2790 - 46*60)/60 = 30/60=0.5 ... Now take integer only which would be 0

and for second

seconds = (2790 - Hour*60-Mintue*60)= 2790-2760-0 = 30 secods...

Now you can create another variable to concate all the values like

Final = Hour : Minutes : Seconds

I hope this will help you.

Use following updated code:

var_hours = tuncate([var_total]/24;0)

var_minutes = truncate(([var_total] - [var_hours]*24)/60;60)

var_seconds = [var_total] - [var_hours]*24-[var_minutes]*60

Thanks,

Swapnil

[former_member182541]

This is seems to be not working after bifurcating till seconds. I have identified the hours by [total seconds]/3600. Now i need the minutes. what i am trying to achieve is taking those decimal values and * 60. How to take only those decimal values. Means 8.33,4.82,63.92 i need only .33,.82.92.

[former_member182541]

Finally I cracked it with taking the total values/3600 and then taking those value into floor() then dividing with them i got the decimal values now i multiplied 60, i got the minutes.

Thanks a lot for your support on this.

[Former Member]

Good to know that you have cracked it.

You can mark my asnwer as correct and close the thread.

Thanks,

Swapnil

[Former Member]

Cell calculations are supported when using bics, can you give it a try?

[former_member182541]

Working on those cell calculations will let you know, but tell one thing does cell restrictions has been solved by SAP ? I hope its a limitation in BO.

[Former Member]

Cell calculations are supported in Web Intelligence using BICS, so in your case I would try to get it working with BEx.