cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Balazs1 Frequent Visitor
Frequent Visitor

Calculating Max waiting time on any given date by group

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 data.JPGDataexpected outcome.JPGExpected outcomeresult, not quite.JPGbecause this doesn't look rightthis was a nice try, but failed.JPGthis seemed a good idea, but failed

2 REPLIES 2
Community Support Team
Community Support Team

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

@Balazs1 ,

 

You may try using MAXXFILTER and DATEDIFF.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Balazs1 Frequent Visitor
Frequent Visitor

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

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