Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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:

   

CategoryMilestone 1Milestone 2
A1/1/20119/10/2011
A1/1/2011
A10/5/201011/10/2011
A1/1/20119/10/2014
A1/1/20119/11/2010
A4/1/20114/3/2011
B1/1/20109/10/2013
B9/11/2013
B3/1/20124/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

   

CategoryMin DurationMilestone 1Milestone 2
A24/1/20114/3/2011
B403/1/20124/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

Re: Show milestone dates of a category that has minimum duration

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

View solution in original post

5 Replies
Not applicable

Re: Show milestone dates of a category that has minimum duration

‌Any help please?

Not applicable

Re: Show milestone dates of a category that has minimum duration

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

View solution in original post

anlonghi2
Contributor II

Re: Show milestone dates of a category that has minimum duration

Hi Annamalai,

try attached qvw.

Let me know.

Best regards

Andrea

Not applicable

Re: Show milestone dates of a category that has minimum duration

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
Contributor II

Re: Show milestone dates of a category that has minimum duration

You are welcome!!!

Regards

Andrea