Problem with aggr over dimension

[#1]

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

[sunny_talwar]

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

[sunny_talwar]

Forgot to attach your application (PFA)

[#4]

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)

[sunny_talwar]

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

Best,

Sunny

[#6]

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.

[sunny_talwar]

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

[#8]

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.

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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