Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis and Max(Date)

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

NameDepartmentStart DateEnd Date
JérômeAgency01/08/200001/01/2012
JérômeTeam GF01/01/201231/12/9999
JérômeTeam GF201/02/201305/11/2013
JérômeTeam GF306/11/201331/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])

NameDepartmentStart DateEnd Datecount
JérômeTeam GF01/01/201231/12/99991
JérômeTeam GF306/11/201331/12/99991

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...

16 Replies
jvitantonio
Luminary Alumni
Luminary Alumni

Hello Anne,

Should you use <= for [EndDate] and >= for [StartDate]?

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


Thanks

JV

Not applicable
Author

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...

jvitantonio
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

Can you something like this


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

Not applicable
Author

Missing a double quote or a parenthesis, expression is not accepted...

Not applicable
Author

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

Not applicable
Author

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

  [Start Date]=

  {"$(

  =MAX( {<[Start Date]={"<=$(=timestamp(vRefDate))"}>}[Start Date])

  )"}

  >} [Name])

jvitantonio
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

Hi JV,

In place of having

NameDepartmentStart DateEnd Datecount
JérômeTeam GF01/01/201231/12/99991
JérômeTeam GF306/11/201331/12/99991

I want to have only one line, max(Start Date) when Start Date <= vRefDate (and EndDate => vRefDate)

NameDepartmentStart DateEnd Datecount
JérômeTeam GF306/11/201331/12/99991

Thanks,

aL