Show milestone dates of a category that has minimum duration

[#1]

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

[#2]

‌Any help please?

[#3 · Accepted solution]

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

[anlonghi2]

Hi Annamalai,

try attached qvw.

Let me know.

Best regards

Andrea

[#5]

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.

Thanks for your help.

-- Malai

[anlonghi2]

You are welcome!!!

Regards

Andrea