Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Problem with aggr over dimension

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

1 Solution

Accepted Solutions
Highlighted

Re: Problem with aggr over dimension

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

View solution in original post

11 Replies
Highlighted

Re: Problem with aggr over dimension

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

Highlighted

Re: Problem with aggr over dimension

Forgot to attach your application (PFA)

Highlighted
Not applicable

Re: Problem with aggr over dimension

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)

Highlighted

Re: Problem with aggr over dimension

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

Best,

Sunny

Highlighted
Not applicable

Re: Problem with aggr over dimension

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.

Highlighted

Re: Problem with aggr over dimension

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

Highlighted
Not applicable

Re: Problem with aggr over dimension

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.

Highlighted

Re: Problem with aggr over dimension

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

Highlighted

Re: Problem with aggr over dimension

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

View solution in original post