Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]))
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]))
Forgot to attach your application (PFA)
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)
So what is the expected output for the chart Count of Status chart?
Best,
Sunny
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.
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
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.
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
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]))