Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Any help please?
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
Hi Annamalai,
try attached qvw.
Let me know.
Best regards
Andrea
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
You are welcome!!!
Regards
Andrea