Issue with Aggr

[ciaran_mcgowan]

Hi All,

I'm having an issue with the Aggr & FirstSortedValue functions. I have a chart that returns both the number of people in a department as well as the top Sales figure/person.

1. The Dimension is the DepartmentID.

2. The Expressions are as follows:

     a. Count(DISTINCT Employee)   //returns the number of people in the department

     b. Max(SamesAmount)               //returns the highest sales figure for that department

     c. FirstSortedValue(Employee,-Aggr(Sum(SalesAmount),Employee))     //returns the name of the best seller for each department.

This works for the most part, except in cases where the highest rated Employee moves to another department. When that happens, the expression 2.b. (above) still returns the maximum Sales figures for the department but the expression 2.c. shows the second highest Employee fort the department.

Actually selecting a department fixes the issue but if there are no selections made (which there normally won't be), the wrong employee name is shown.

Any help would be appreciated!

[Oleg_Troyansky · Accepted solution]

I think the problem must be hiding somewhere in your data model... The Department that Employee belongs to, seems to be a "slowly changing dimension". Where do you store the relation between Employees and their Departments? I think the right way in your case is to keep that relation in the Sales fact table. Then, every Sales record belongs to the Employee and the Department at the time of the change.

If the data is organized like that, I can't see any reason for the problem that you described.

Secondly, I'd synchronize the definitions of max sales in expressions b. and c. The Max sales expression should look like this:

max(Aggr(Sum(SalesAmount),DepartmentID, Employee)) )

Notice that I added DepartmentID to the list of the AGGR dimensions - you have to do it in a chart with DepartmentID as a chart dimension. The same is needed in the other AGGR.

cheers,

Oleg Troyansky

Come and learn Set Analysis and Advanced Aggregation with me at www.masterssummit.com - take your QlikView skills to the next level!

[ciaran_mcgowan]

Hi Oleg,

I don't think the existing data model lends itself to that approach. The relationship between the Sale, Department and Employee tables are linked using the Department ID. In other words, when a department is selected, all Employees and all Sales are displayed. The master calendar is linked to the Sales table so selecting a date will show all sales for that period but all Employees for department the sale went against is displayed. The Emploee has a Start/End date (for the department)  stored in the Department table.

[Oleg_Troyansky]

Hi Ciaran,

it's your data model, and you are the only one who can decide what's good for you.

My point is - with your data model, several issues will distort the results when employees move from one department to another. For this reason, I believe it's generally better to keep the "moving parts" in the fact table, so that every fact record is associated with the correct Employee and the correct Department, even when changes happen.

cheers,

Oleg