Forum Discussion

Anonymous

Not applicable

6 years ago

Cycle Time Calculation

I need some assistance in pulling the cycle times for each task for each project and taking the average for each task where the task is the same in another project under the same customer. We hav...

Resident Rockstar

6 years ago

Hi Anonymous,

this is a bit confusing to me. Could you create a sample report where you recreate your issues and share it?

Cheers,
Sturla

Anonymous
Not applicable
6 years ago
Hi,

Sorry was a bit slow to put it together, hopefully you can review still. As you can see i am able to produce most of what i need but when it is build with millions of records it times out.

Cycle Time Report Sample
- sturlaws
  Resident Rockstar
  6 years ago
  That helps a little, now I know you data structure.
  
  I think there is something missing in your model. On the page "Data I am able to pull", in the bottom table visual, you have [Customer], [Cycle Time], [begin date value], [end date value] and [average time]. [begin date value], [end date value] and [average time] are measures calculated with values from 'Data'. But there is no relationship between the tables 'Data' and 'RefTable'. So in the mentioned table, for each combination of [Customer] and [Cycle time], the 3 measures are calculated over the entire 'Data' table. Which works fine when the table is small, and less fine when there is 2,5 million rows. In addition to being slow, it will also show you the wrong numbers, because [Customer] does not impact/filter the data when the measures are computed. You can see this in your table as well, all 3 measures show the same for the 2 customers.
  
  To remedy this, and get the numbers you want, you need a customer reference in the 'Data' table.
  
  Cheers,
  Sturla