aggr & rank & if

[jeshil09]

Suppose that we want to find which Employees participated in the n latest Category events in a Company?

Events are grouped in Categories (bigger the number the later it is), so an Employee would be displayed as participating in a Category only if he participated in all events in that Category.

Records for an employee look like below:

employee id	Category	Event	Participation
5	10	Workshop	1
5	10	Seminar	1
5	11	Training	0
5	11	Meeting	1
5	12	Brainstorming	1
5	12	Other	1

Employee id =5 participated in Category 10 and 12 below, since he participated in all events under that Category but he did not participate in Category 11 (since he took part only in one event and not in both of them)

So if we want to see which employees participated in the latest 1 Category (=> cat id 12) this employee id would show

but in the latest 2 categories this employee would not show.

I am struggling to write the appropriate expression here..and i want to show above rows but only if employee id qualifies as participating in the latest n category events.

Calculating it in the script would not help since the number of categories should be dynamic and input by the user. However in the script i can calculate a flag 1 or 0 whether an employee participated in the category or not.

[adamdavi3s]

Could you possible expand your table and example with a couple more employees?

Happy to help but struggling to see the whole requirement

[jeshil09]

I managed to do this by calculating a flag for each employee and category and then found the proper expression as

=if(Sum(total if(aggr(rank(Category),employeeid,Category) <= $(vCat), aggr(Sum(NotParticipate),employeeid, Category))) =0,1)

vCat the variable that servers as the input for the latest categories. NotParticipate a field calculated in the script.

if Sum(NotParticipate) would be greater than 0 then it would mean that employee failed to participate in some event in that category. By using this exp as calculated dimension and  Suppress null I am able to hide the employee rows i don't want to see.

While in the script the tables I created for this flag calculation are:

tmp:

NoConcatenate

Load employeeid,

           category,

           count(*) as EventCount,

           sum(participation) as CatPart

Resident OriginalTable

Group by employee,category;

tmp2:

NoConcatenate

Load employeeid,

          category,

         if(EventCount = CatPart, 0, 1) as NotParticipate //using 0 instead of 1 so that we can use sum in the expr later

Resident tmp;

left join(OriginalTable)

Load employeeid,

          category,

          NotParticipate

Resident tmp2;

Drop tables tmp, tmp2;

Hopes it helps sb else with similar conditions.

[jeshil09 · Accepted solution]

Actually in the end I had to tweak a bit the expression so that it worked for all employeeids, the earlier exp worked only when an employeeid was selected.

Still maintaining the changes in the script for the flag, the exp in the chart is

=if(aggr(sum(if(aggr(rank(Category),EmployeeId,Category) <= $(vCat), NotParticipate)),EmployeeId) = 0,1)

Meaning that we firstly have the table having rank of categories by employee and if that rank is equal to the latest categories we want to display, we count the flags and the sum of those flags for an employee should be zero, otherwise he did not participate in one event in that Cateogry.