- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Calculating Max waiting time on any given date by group

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-18-2019 06:17 PM - edited 02-18-2019 06:29 PM

Hi,

I'd like to ask your help with the following issue:

I have a table, teams, starting and finishing jobs. When the finish field is empty it means it's still ongoing. I'd need a table/graph, which shows me on any day what was the maximum waiting time. Let's say it's 04/Feb, row02 doesn't play anymore, only rows 03, 04 and 05. And then on 04/Feb the max waiting time was 2 days. I tried the below calculated table, but that doesn't return quite right, because altough it returns the correct row, but the corresponding result gives maximum waiting time always for today and not for any past days.

(The data sample and expected result is not the same as the sample calculated table and graph.)

Assistance is much appriciated.

I have a table

## Re: Calculating Max waiting time on any given date by group

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-19-2019 08:10 PM

## Re: Calculating Max waiting time on any given date by group

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-21-2019 11:54 AM - edited 02-21-2019 12:02 PM

Hi,

I tried to add the DATEDIFF, but it's proved too rigid to a calculated table. The condition is that on a given day the start date is either the same or eariler __and__ the finish date is either empty or later, but there is no set of fix dates apart from this conditions. I was thinking on the possibility that there is no solution for this question, but the funny thing is the that the above calcuated table work brilliantly with aggregated functions, ie. count of row, or total waiting time, even average waiting time. The issues came when I search on a single value, such as MAX, but on any row. The below table is still functioning, just not giving me the answer I need, it returns the MAX as of TODAY, but not for historical states. Let's say on the example the parent row of the result gives 400 days today, but then it should be 399 for yesterday, and so on, and so on. Consequently the at some point of time the parent of the MAX likely to have been a different row as the 400 gradually goes down to zero and there are other rows in the dataset, but as the graph shows that's part is functioning. I think the problem may be, that actually could be more than one result fit to cirterias.

Thanks,

Balazs