I have different Documents and each document has different reports signed to it Each Report has a statustype and a statusiD assigned to it.
I'm trying to build a chart which shows me the minimum and maximum duration of a report where the status is VL.
Enclosed you will find an excel sheet and a sample QVW of what I have tried so far.
If you select Document with ID 1. You will see that the document has 1 report which has been altered 5 times. This report has had the status VL 3 times. I'm only interested to show the duration of the first and last time when it had the status VL.
I hope you guys can help me out. I'm kind of stuck
There is a mismatch in your data (qvw) and the Excel regarding ReportID and DocumentID. I might have used the wrong attributes below.
Can you try
if(Status = 'VL' and (End-Start=aggr(min(total End-Start),ReportID) or End-Start=aggr(max(total End-Start),ReportID)), End - Start)
it will add the condition to be either the min or max enddate. this work with your example data because the start date is always the same for a ReportID. The "total" is used to look at all rows of the ReportID.
It might also be worth to calc the duration during the load to get a better view and less cluttered expressions
