1 2 Previous Next 25 Replies Latest reply on Apr 25, 2019 8:33 AM by Ewald Hofman

Match order # based on call time

I'm matching order #'s to calls based on the order time and call time.  I need to find a way to match an order # to call when the order # doesn't fall between the beginning and end time of the call. I need to match it to the closest call time prior to the order time. The last 2 items in the list below are the ones that don't match a call time.

• 1. Re: Match order # based on call time

You can take the following approach

1. Create a calculated field that determines per row whether it was included in the run or not

[Begin Time Adjusted 1 Hour] <= [Order date/time] AND [Order date/time] <= [End Time Adjusted 1 Hour]

2. Then create a calculated field that gets the previous end time using an LOD. It gets per order the MAX(End Time) that is smaller than the current Order date/time

{FIXED [Order #] : MAX(IF ([Order date/time] > [End Time Adjusted 1 Hour]) THEN [End Time Adjusted 1 Hour] END) }

3. Now create a calculated field that determines per order (again using an LOD) whether the order has a time that is between the start and end of a run

{FIXED [Order #] : SUM(IF [Was created in this run?] THEN 1 ELSE 0 END)} > 0

4. Now you can piece it all together.

IF [Has a call time?] THEN {FIXED [Order #] : MAX(IF [Was created in this run?] THEN [End Time Adjusted 1 Hour] END) }

ELSE [Previous End Time]

END

• 2. Re: Match order # based on call time

Hi Ewald,

This is almost perfect. The only adjustment I need is to be able to see what the call type was for each call. I uploaded an updated workbook.

So for example, for Apple, the call at 3:05:52 am, was that a flowers call or a fruits call.

• 3. Re: Match order # based on call time

Also, do you know if there is a better way than doing a full outer join? Looking at my actual data, the tables are going to be HUGE this way since every order number is listed for every call time. I don't know if there is another way around it. I'm joining sql and excel right now.

• 4. Re: Match order # based on call time

You can use this expression for it

{FIXED [Order #] : MAX(IF [End Time Adjusted 1 Hour] = [Call time] THEN [Call Type] END) }

• 5. Re: Match order # based on call time

You can discard all entries for which the End time is later than the order date as you will not consider these as valid options

• 6. Re: Match order # based on call time

Hi Ewald,

Thank you so much! I was worried this wouldn't be possible. I will go through and validate my data to make sure all aligns. I'll let you know if I run into any issues.

Thanks again!

Barb

• 7. Re: Match order # based on call time

Hi Ewald,

I started validating. What I'm missing is the calls that didn't have an order. I need to be able to see all calls in the view. If there is no order for that call then the order # would be blank. Can that be done?

Barb

• 8. Re: Match order # based on call time

Can you provide a sample workbook which includes those missing order numbers? The workbook that you provided earlier doesn't seem to have these.

• 9. Re: Match order # based on call time

Hey Ewald,

I'm not missing order #'s, just missing calls. If you look at the workbook you uploaded, you'll see a tab labeled calls. You'll see there are 19 calls there for Banana. But when you look on the tab labeled NEW, there are only 11 calls where orders were made.

What I'm trying to do is count the total number of calls per day per operator and count how many orders they had. I'll create a conversion %, total orders/total calls handled. Then I want to be able to roll that up to get a site level number.

Barb

• 10. Re: Match order # based on call time

Attached the workbook to show you the conversion that I tried to start. I have the total calls handled and the total order count, but I need to be able to break it down by call type. This is giving me the same number regardless of call type.

• 11. Re: Match order # based on call time

Instead of using an INCLUDE LOD, I used an EXCLUDE LOD to exclude the begin and end time

Total calls:

{EXCLUDE [Begin Time Adjusted 1 Hour], [End Time Adjusted 1 Hour] : COUNTD([Begin Time Adjusted 1 Hour]) }

Total orders:

{EXCLUDE [Begin Time Adjusted 1 Hour], [End Time Adjusted 1 Hour] : COUNTD([Order #]) }

Conversion %

SUM([Total orders]) / SUM([Total calls])

• 12. Re: Match order # based on call time

Looks like the order # isn't right here. It's showing the same order total for both call types.

• 13. Re: Match order # based on call time

(oops), you should not use the "Call Type" field, but the "Closest Call Type". Both as the dimension in your Viz, as well as in the LOD in the calculated field "Total orders"

{FIXED [Closest Call Type], [Operator ID] : COUNTD([Order #]) }

Because you changed the dimension to Closest Call Type, you will have duplication of data for Call Type. You can fix this by modifying the expression in "Total calls" to

{EXCLUDE [Begin Time Adjusted 1 Hour], [End Time Adjusted 1 Hour] : COUNTD(IF [Call Type] = [Closest Call Type] THEN [Begin Time Adjusted 1 Hour] END) }

• 14. Re: Match order # based on call time

Thanks! That works. Is there any way to get a grand total?

1 2 Previous Next