topic Re: Set Analysis and Max(Date) in QlikView

Set Analysis and Max(Date)

Mon, 17 Mar 2014 16:38:22 GMT

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

Re: Set Analysis and Max(Date)

jvitantonio — Mon, 17 Mar 2014 16:44:27 GMT

Hello Anne,

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

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

Thanks

Re: Set Analysis and Max(Date)

Mon, 17 Mar 2014 16:48:31 GMT

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

Re: Set Analysis and Max(Date)

jvitantonio — Mon, 17 Mar 2014 16:50:26 GMT

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.

Re: Set Analysis and Max(Date)

Mon, 17 Mar 2014 17:03:30 GMT

Can you something like this

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

Re: Set Analysis and Max(Date)

Mon, 17 Mar 2014 17:34:08 GMT

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

Re: Re: Set Analysis and Max(Date)

Mon, 17 Mar 2014 17:38:08 GMT

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

Re: Set Analysis and Max(Date)

Mon, 17 Mar 2014 17:41:00 GMT

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

[Start Date]=

{"$(

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

)"}

>} [Name])

Re: Set Analysis and Max(Date)

jvitantonio — Mon, 17 Mar 2014 17:42:22 GMT

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

Re: Set Analysis and Max(Date)

Mon, 17 Mar 2014 17:47:48 GMT

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,

Re: Set Analysis and Max(Date)

Mon, 17 Mar 2014 17:51:59 GMT

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

Re: Set Analysis and Max(Date)

Mon, 17 Mar 2014 18:31:13 GMT

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])

Re: Set Analysis and Max(Date)

Tue, 18 Mar 2014 12:47:59 GMT

did the solution work ?

Re: Set Analysis and Max(Date)

Tue, 18 Mar 2014 13:13:47 GMT

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

Re: Set Analysis and Max(Date)

Tue, 18 Mar 2014 13:54:19 GMT

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

Re: Set Analysis and Max(Date)

Tue, 18 Mar 2014 14:06:29 GMT

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])

Re: Set Analysis and Max(Date)

Tue, 18 Mar 2014 14:48:36 GMT

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)