Set Analysis and Max(Date)

[#1]

Hello everybody!

I know there were already some topics on this subject, but I coudn't find any solution for my problem.

I have some historical data under the following form (31/12/999 beign the representation of no end date, current value)

Agency01/01/2012

Name	Department	Start Date	End Date
Jérôme	Agency	01/08/2000	01/01/2012
Jérôme	Team GF	01/01/2012	31/12/9999
Jérôme	Team GF2	01/02/2013	05/11/2013
Jérôme	Team GF3	06/11/2013	31/12/9999

In an ideal world I shouldn't have 2 values with end date = '31/12/9999' but it's the way the provider works in some incorrect cases.

I want to be able to identify to which department Jérôme belongs at a certain date, input in a Calendar object, in a variable called vRefDate.

I have created the following expression but in that case it returns the both values

count({ $  <[EndDate]={">=$(=timestamp(vRefDate))"},[Start Date]={"<=$(=timestamp(vRefDate))"}> } [Name])

Name	Department	Start Date	End Date	count
Jérôme	Team GF	01/01/2012	31/12/9999	1
Jérôme	Team GF3	06/11/2013	31/12/9999	1

How can I have only the last line??? I couldn't find a way to use a max date or a first sorted value, but I probably did something wrong 😞

Anybody can help? Thanks in advance!

Ce message a été modifié par : Anne-Lise Freson Add an attachment of a simplified application to show the problem...

[#2]

Still some problems, it shows nothing, and the editor still notes the <= in red, I think it get lot with the quotes

[#3]

try this, it is working as you expected

=count({ $  <[Date fin Service]={">=$(=timestamp(vRefDate))"},

[Date début Service]=

  {"$(=

  Date(MAX({<[Date début Service]= {'<=$(=timestamp(vRefDate))'}>}[Date début Service]),'DD/MM/YYYY')

  )"}

  > } [ID Individu])

[#4]

did the solution work ?

[#5]

Sorry I was in a meeting this morning, so I could only test it now... and it works great 🙂

thank you very much for your help, and now I know how to manage imbrication with double quote and signle quote...

[#6]

Finally I was a little too optimistic It works perfectly in the sample application I gave you, but if I have other persons, with different dates, it fails as it takes the max(date) whatever the person. However I need the max(date) from each person...

So if I add cyril in my data...

Name	Department	Start Date	End Date
Jérôme	Agency	01/08/2000	01/01/2012
Jérôme	Team GF	01/01/2012	31/12/9999
Jérôme	Team GF2	01/02/2013	05/11/2013
Jérôme	Team GF3	06/11/2013	31/12/9999
Cyril	IT	29/11/2013	31/12/9999

... I need to have

Name	Department	Start Date	End Date	count
Jérôme	Team GF3	06/11/2013	31/12/9999	1
Cyril	IT	29/11/2013	31/12/9999	1

If I'm using the proposed expression, I only got

Name	Department	Start Date	End Date	count
Cyril	IT	29/11/2013	31/12/9999	1

[#7]

try this ... if this doesn't work. please provide more data in ur sample app

=

count({ $  <[Date fin Service]={">=$(=timestamp(vRefDate))"},

[Date début Service]=

  {"$(=

  Aggr(Date(MAX({<[Date début Service]= {'<=$(=timestamp(vRefDate))'}>}[Date début Service]),'DD/MM/YYYY'),[Nom & Prénom])

  )"}

  > } [ID Individu])

[#8]

Does not work... Here is a new version of the application with 2 more people so you can see (I put back again my intial expression so you can see the value)