Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Show milestone dates of a category that has minimum duration

Hi,

I want to show the milestone dates for a category that has the minimum duration. For example consider the below data set:

 Category Milestone 1 Milestone 2 A 1/1/2011 9/10/2011 A 1/1/2011 A 10/5/2010 11/10/2011 A 1/1/2011 9/10/2014 A 1/1/2011 9/11/2010 A 4/1/2011 4/3/2011 B 1/1/2010 9/10/2013 B 9/11/2013 B 3/1/2012 4/10/2012

The duration is calculated as (Milestone 2 - Milestone 1) and any negative numbers needs to be ignored. My chart should show the Category, Minimum duration between milestone dates and the milestone dates associated with the minimum duration. My chart should look as below

 Category Min Duration Milestone 1 Milestone 2 A 2 4/1/2011 4/3/2011 B 40 3/1/2012 4/10/2012

I am able to show the minimum duration ignoring negative numbers but not sure on how to show the Milestone dates associated with it. Attaching a sample qvw for easier understanding. Any help would be greatly appreciated.

Thanks,

Malai

1 Solution

Accepted Solutions
Not applicable
Author

If anyone else is interested, I was able to solve this using the below expression.

Aggr(FirstSortedValue([Milestone 1],  If( ([Milestone 2] - [Milestone 1]) < 0, null(), ([Milestone 2] - [Milestone 1]))), Category)

Thanks,

Malai

5 Replies
Not applicable
Author

Not applicable
Author

If anyone else is interested, I was able to solve this using the below expression.

Aggr(FirstSortedValue([Milestone 1],  If( ([Milestone 2] - [Milestone 1]) < 0, null(), ([Milestone 2] - [Milestone 1]))), Category)

Thanks,

Malai

Creator II

Hi Annamalai,

try attached qvw.

Let me know.

Best regards

Andrea

Not applicable
Author

Hi Andrea,

That was helpful. I have a complicated data model and based on the filter selection, the min duration will change and the milestone dates will also change accordingly. So instead doing the calculation in the script it has to be on the front end. I was able to achieve it using FirstSortedValue. Appreciate your help and in fact you solution helped me with another problem I was facing.