7 Replies Latest reply on Jul 6, 2017 1:39 PM by Shinichiro Murakami

# Total time taken calc.

Hello folks, Sorry this is probably a big one. Please bear with me here, so  I walk you through as best I can.

Here is a sample data set and workbook (v10.0). Goal is to track avg. time/days each queue takes (other dimensions can be used for slicing and dicing as well).

Some non-pertinent info:

- Each Job will move through multiple "Queues".

- Queues will be processed by Owner/Processor

- All jobs will have Queue 1 and 7 mandatory.

- But not all jobs will go through rest of all queues.

-Start and End times are available, Days taken is available in the datasource as well.

Here is the Workbook snapshot, which tracks "Avg. Days taken" for each "Queue".

Analysis is only meant to display "Avg Days taken" measure by any of the dimension. Which it does well. Straightforward so far.

Now the problem statement:

Whereas the user did something I did not expect, they ctrl clicked Queue 1 and 2 and said they wanted to find out how much time it takes from Queues 1 and 2 together. Or say Queues 1-4 altogether.

Problem is, Queue 1 is short, doesnt take more than 5 minutes. Hence, its skewing the averages.  From the user perpective, it cannot be 5.5 days. they would expect to see an addition of both averages...

I tried explaining this is not intended to be used this way, Nor is the dashboard capable of answering such a question.

Do you think this a right question and meant to be answered this way? - If so, then can I tweak the calculation using LOD to add the averages of Queue 1 and Queue 2?

Thanks much if you got through all the way here!!

• ###### 1. Re: Total time taken calc.

HI Karthink

Hard stop to select multiple.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Total time taken calc.

Shinichiro,

Actions are working fine. But, selecting 2 or more avg's on the left worksheet and expecting to See a Sum() is where I was confused.

• ###### 3. Re: Total time taken calc.

Hi Karthink

Here you go.

You can use table calc with filtered by index.

Thanks,

Shin

• ###### 4. Re: Total time taken calc.

Thanks a lot Shinichiro. You are certainly doing your magic.

But, when I used plain excel to calculate for how long it takes for first 3 queues in total. I do this. First sum the queues within each case (including if a queue has multiple recurrences, and then Avg, of those 3 is 22.66 days. Which seems to be logical.

If I pull up all records, I see 76.6 days instead of 100+ days that your solution shows. Could you please check?

Also, this should reflect for other charts too. When we slice and dice, it does not seem to give right answer.

• ###### 5. Re: Total time taken calc.

HI Karthik

I'm sorry I'm not following your logic,

The calculation simply add this value.

What is in the excel column A~D?

I'm sure I don't yet understand your expected logic.

Or attaching Excel file is the fastest way.

And this uses table calc, and table calc should be set on each individual Worksheet.

It's very fragile based on the which dimension / detail is included in each worksheet..

Thanks,

Shin

• ###### 6. Re: Total time taken calc.

Woohoo!! I think I have it. I created a copy of the measure and put this one side by side.

{INCLUDE  [Job ID]: SUM([Days Taken])}

Tried for both 3 queues and All queues.

Kinda suspected the solution was probably in LOD's. Thanks a tonne Sunichiro !

1 of 1 people found this helpful
• ###### 7. Re: Total time taken calc.

Congrats.!

Thanks,

Shin