Skip to Content

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

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    Posted on May 21, 2013 at 04:34 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 21, 2013 at 02:05 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 21, 2013 at 04:41 AM

    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


    Time.JPG (31.2 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 29, 2013 at 11:26 AM

    Hi,

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

    with Regards,

    Antony Jerald.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.