Need help for comparing time(hr:min:secs) variants in Bex Query

[Former Member]

Hi,

I've been stuck with a requirement for how to proceed with it.  Can you help me out in this?  the following are the bex query i've generated:

Sales office	Order Number	Order Count	Order Cycle Time
1500	1011	1	2:04:09
1501	1012	1	1:04:55
1501	1013	1	00:04:34
1503	1015	1	4:08:08

From the above report, I need to add one more column which is Order < 2 Hrs.  i.e., we have order number and order cycle time.  I'll have to generate Orders count less than two hours based on Order number and order cycle time and the following is it's logic:

if (Order cycle time < 2 hrs)

then count(Order number)

The following is it's output:

Sales office	Order Number	Order Cycle Time	Order < 2 Hrs
1500	1011	2:04:09	0
1501	1012	1:04:55	1
1501	1013	00:04:34	1
1503	1015	4:08:08	0

Here order number is a dimension.  Order cycle time is a formula variable having formula dispatch time - receipt time.

Here, basically I could not set condition for order cycle time.  Because, Bex is not taking up it's time i.e., Order Cycle Time < 2 is not taking in Bex.  Ideally if it's order cycle time < 2:00:00 seems to be accepted in BEx.  But I don't find : option in Bex query.

Request you to please help me to do this report.

with Regards,

Antony Jerald.

[anshu_lilhori · Accepted solution]

Hi,

Make use of TIME function It is avaiable under data functions in Bex query designer.

Make a a new formula Say F1 like this TIME (7200) this will give you 02:00:00.

Now based on this value you can do your comparision.

Hope that helps.

Regards,

AL

[Former Member · Accepted solution]

Hi Anshu Lilhori,

Thank you so much for your valuable inputs. 

with Regards,

Antony Jerald.

[anshu_lilhori · Accepted solution]

Hi,

What happened you open the thread again ?

Is your issue not resolved ?

Regards,
AL

[Former Member · Accepted solution]

Hi,

Ya actually I thought the issue had resolved.  But I could find the issue, while validating, is also with Order Cycle Time(OCT) formula which I've created. So mentioned the same and reopened this thread again.  Will update you the required.

with Regards,

Antony Jerald.

[Former Member]

Hi,

I've asked to install Patch file in all clients machine as suggested by SAP OSS message.

with Regards,

Antony Jerald.

[anshu_lilhori]

Hi.

So now its working as required.

Regards,

AL

[Former Member]

Not yet checked because, the patch which is required for sap netweaver 7.30 is not yet released to customers in the market 😞

If you don't mind, can u plz help me to solve the thread: http://scn.sap.com/thread/3366570 ?  I've been searching for solution for many days but could not able to sort it out.

Regards,

Antony Jerald.

[Former Member]

Hi I need help from you again for the same time dimension.(I'm adding the following to original post)

I've created the following formula:

Order Cycle Time(OCT) = Time(((Receipt Date - order creation date)*86400 + Receipt time - Order Created time))

Let us take the following example:

Order Creation date:01.05.2013

Receipt Date: 02-05-2013

Order Creation time: 00:06:21

Receipt time: 12:06:00

Ideally I should get OCT = 35:19:46.  But due to some reason, for the above value, in BEx explorer, I'm getting BEx value for OCT = 11:19:46(but in its formula bar of BEx analyzer, it's showing as 01-01-1900  11:19:46).  If I copy this value(11:19:46) and paste it in a new excel sheet and convert the same to Time format from excel, I'm getting the exact value as 35:19:46.

Henceforth, its difficult do the same conversion for each and every cell by copying the entire column and doing the same conversion in excel sheet.  Can you please suggest the best approach which I can follow and hence the same corrects this issue?

Here, Order Creation date, receipt date are date dimension brought to key figures through by creating variables referencing to that particular dimensional infoobjects and Order creation time and receipt time are time dimension.

with Regards,

Antony Jerald.

[anshu_lilhori]

Hi,

Is it displaying the correct result in RSRT or web or portal .

Please confirm.

Regards,

AL

[anshu_lilhori]

Hi,

Check these SAP Notes

http://www.stechno.net/sap-notes.html?view=sapnote&id=1634823this

I think this is the concern:

Processing a Value as a Time

TIME

The TIME operator takes any key figure or (partial) formula in general and makes a TIME type key figure. Go to the formula editor and, for the operator, double-click the data functionProcess Value as Time and, for the operand, double-click the key figure you want. For example, the formula is then Time (Sales).

The system cuts off the decimal places, interprets the value as seconds, and displays the value in the form +-hh:mm:ss. For example, -4812 is displayed as –1:20:12.

Any time is seen as seconds from 00:00:00 and output in hours, minutes and seconds. A TIME type key figure with value 0 is displayed as 00:00:00.

Negative times are permitted: With a negative time the system calculates from 00:00:00 backwards.

The number of hours can be any amount. After 24 hours the system restarts at 0.

Regards,

AL

[Former Member]

Hi,

Ya you are correct I've executed the report in RSRT transaction.  There I'm getting the correct result as required.

What can I do for the same where for the correct result which is not appearing in BEx analyzer?

Plz suggest...

with Regards,

Antony Jerald.

[anshu_lilhori]

Hi,

I just checked on my system and i am getting the time calculated properly after 24:00:00 hrs as well.

So you also check for the same.

Make simple formula say F1 like TIME(95000) and check what value does it display as this will be greater than 24:00:00.

Regards,

AL

[anshu_lilhori]

Hi,

I think you can look for some SAP Note which addresses this issue or you can raise an OSS with SAP.

In the meantime if i get some note on the same then will update you.

Regards,
AL

[Former Member]

Ya I've raised an OSS message.  Will update you once I solve the issue 🙂

Thanks for your solution.

with Regards,

Antony Jerald.

[former_member182470]

Hi,

Step 1: Create Order count formula variable. Hope it is already available in your case

Step 2: You need to create a range variable to get < 2 hrs. You will get all operators like less tha, greater than etc..

Step 3: Keep step1 and step 2 under New selection of your Order Count KF

Regards,

Suman