topic Re: Problem with aggr over dimension in QlikView

Problem with aggr over dimension

Wed, 29 Apr 2015 10:19:26 GMT

Hello, I am building an application to show some training courses statistics. Courses have expiration dates and when they are expired they need to be taken again.

For example person 7096 took the course 842 second time and it is compliant at the moment.

%PersonnelNumber	Course Number	Course Date	Expiry Date	Status
00007096	00000842	29/03/2012	28/03/2014	Non Compliant/Expired
00007096	00000842	10/04/2014	09/04/2016	Compliant

Now I want to show status for the employees of their courses but obviously I do not want to show ‘Non Compliant’ if the expired course was retaken.

I try to create chart and table using aggregation function. I try to aggregate data for given person and course only with maximum date

count(Aggr(max([Course Date]),[%PersonnelNumber],[Course Number]))

But it is not working. When I try to prepare a table or a chart with Status dimension I get a correct count of 1 but for ‘Non Compliant’

What I do wrong? I am attaching example application with one employee and charts to show the problem.

Any help would be appreciated,

Regards,

Mat

Re: Problem with aggr over dimension

sunny_talwar — Wed, 29 Apr 2015 10:22:22 GMT

Hey Mat -

Are you looking for a count 1 for both Compliant and Non-Compliant? If yes then try this:

=Count(Aggr(max([Course Date]),[%PersonnelNumber],[Course Number], [Course Date]))

Re: Problem with aggr over dimension

sunny_talwar — Wed, 29 Apr 2015 10:23:08 GMT

Forgot to attach your application (PFA)

Re: Problem with aggr over dimension

Wed, 29 Apr 2015 10:26:59 GMT

Thanks, but it is not exactly what I want. For status Non-Compliant I want to show 0. I want to count only the newest courses (aggregation on course number and person)

Re: Problem with aggr over dimension

sunny_talwar — Wed, 29 Apr 2015 10:30:01 GMT

So what is the expected output for the chart Count of Status chart?

Best,

Sunny

Re: Problem with aggr over dimension

Wed, 29 Apr 2015 10:37:01 GMT

It would be something like:

Status	Count of Status
Compliant	1
Non Compliant/Expired	0

But best to understand what I want is to look at the pie chart I have added. It is all red = 100% Non compliant.

It should be all green (for course 842) = 100% compliant as the newest course is compliant.

Re: Problem with aggr over dimension

sunny_talwar — Wed, 29 Apr 2015 11:02:55 GMT

Try this and see if it works for other selections as well (working for your current selection)

Count(If(Date(Aggr(Max(TOTAL <%PersonnelNumber, [Course Number]> [Course Date]), Status)) =

Date(Aggr(Max([Course Date]), Status)), [Course Date]))

HTH

Best,

Sunny

Re: Problem with aggr over dimension

Wed, 29 Apr 2015 12:49:09 GMT

Unfortunately it does not work properly. It always shows 1 on pie chart - it takes only one status for last date.

If I select a few persons and/or a few courses it needs to count all the statuses for persons with given courses.

Re: Problem with aggr over dimension

sunny_talwar — Wed, 29 Apr 2015 12:54:00 GMT

Hahahaha, somehow I had an intuition that it won't work as you would expect. But I think it is a step in the right direction. Let me further explore it.

Best,

Sunny

Re: Problem with aggr over dimension

sunny_talwar — Wed, 29 Apr 2015 12:59:25 GMT

Can you try this:

Count(If(Date(Aggr(Max(TOTAL <%PersonnelNumber, [Course Number]> [Course Date]), %PersonnelNumber, [Course Number], Status)) =

Date(Aggr(Max([Course Date]), %PersonnelNumber, [Course Number], Status)), [Course Date]))

Re: Problem with aggr over dimension

Wed, 29 Apr 2015 14:44:57 GMT

Sunny, I think it works now. Was much more difficult than I thought but good that it finally works.

Thanks

Re: Problem with aggr over dimension

sunny_talwar — Wed, 29 Apr 2015 14:46:16 GMT

Hahahaha ya, I have been in your place many times. But I am glad we finally found the solution.

Best,

Sunny