Problem with showing values without the status 'End'

[#1]

Hi,

Im making an applications with different tables with many data. Lets look at his example what causes the trouble (This is just an example, with dummy data to make the problem clear):

ID	STATUS	Begin date	End date
1	Start	1-1-2001	4-1-2001
1	Middle	4-1-2001	8-1-2001
1	almost finished	8-1-2001	18-1-2001
1	End	18-1-2001	23-1-2001
2	Start	1-1-2001	3-1-2001
2	Middle	3-1-2001	8-1-2001
2	almost finished	8-1-2001	18-1-2001
2	End	18-1-2001	21-1-2001
3	Start	1-2-2001	4-2-2001
3	Middle	4-2-2001	8-2-2001
3	almost finished	8-2-2001	18-2-2001

What i want is to show the ID's that not have ended, with the amount of days from the start. So today() - Begin date where the ID dont have an End status. If i do a not wildmatch on the word 'End' it still shows the ID cause of the same ID numbers with another status.

Does somebody know an solution for this?

*Im using the personal edition, so i can't open QVW files from others.

[#2 · Accepted solution]

Hi dbuijsman,

You will have to use set analysis for this expression:

Min({<ID = ID - {"=Count({1<STATUS={'End'}>} ID) > 0"}>} BeginDate)

This way you will have the minimum begin date for all IDs, except for those which have at least one STATUS = End. From this, you can substract from Today() to have the duration in days:

JG

[#3]

Thanks it worked!