Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
Hello Anne,
Should you use <= for [EndDate] and >= for [StartDate]?
count({ $ <[EndDate]={"<=$(=timestamp(vRefDate))"},[Start Date]={">=$(=timestamp(vRefDate))"}> } [Name])
Thanks
JV
I simplified the example, but I really have timestamp in my database and not simple dates...
I got problem in my expression when vRefDate >= 06/11/2013 in this example... In all other cases it works...
Hello Anne, this is the same. Make sure that EndDate and StartDate are also Timestamp. If you attach your app I can help you better.
JV
Can you something like this
count({ $ <[EndDate]={">=$(=timestamp(vRefDate))"},[Start Date]= {"$(=MAX( {<[Start Date]={"<=$(=timestamp(vRefDate))"}>} [Start Date]) > } [Name])
Missing a double quote or a parenthesis, expression is not accepted...
Here is a simplified application (sorry, security is important), where you can find the problem (aggregation of ETP = 2 in place of 1, and 2 lines for actual department/service)... Hope it helps
=count({$<[EndDate]={">=$(=timestamp(vRefDate))"},
[Start Date]=
{"$(
=MAX( {<[Start Date]={"<=$(=timestamp(vRefDate))"}>}[Start Date])
)"}
>} [Name])
Anne, what value would you like to have as a result? The expression is working fine as per your set analysis. Tell me exactly what you need to show and I can try to fix it. JV
Hi JV,
In place of having
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 |
I want to have only one line, max(Start Date) when Start Date <= vRefDate (and EndDate => vRefDate)
Name | Department | Start Date | End Date | count |
---|---|---|---|---|
Jérôme | Team GF3 | 06/11/2013 | 31/12/9999 | 1 |
Thanks,
aL