Set Analysis - Values outside Dimension in Straight Table

[Anonymous]

Thank you for any help, for some reason, this just isn't clicking on  how to do this, and I will be so appreciative for any help.  Here is a sample of what I need to do:

LOAD * INLINE [

    Manager, Employee, Level

    , John, 1

    John, Sara, 2

    John, Marsha, 2

    John, Jane, 2

    Sara, Simon, 3

    Sara, Ralph, 3

    Sara, Mickey, 3

    Marsh, James, 3

];

I want a straight table with the dimension to be 'Level', and the expression to be the count of employees at the level below.  So for example, at level 1, I want a count of employees at level 2.  At level 2, I want a count of employees at level 3.  I do not want to use the before/below/etc function in QlikView to do this calculation (because this is just a small example and using this doesn't solve my bigger problem).  It seems logical that there is a way I could do this in set analysis with the Aggr function on maybe 'Manager', but I can't figure it out.  So in the end, without below, before, etc, I want a straight table that shows:

Level   Count of Employees

1          3

2          4

Thank you for your help!

[swuehl]

If you want to use set analysis, set analysis will be evaluated once per chart, so it won't consider the dimensional value.

But for few levels, you can use multiple expressions with set analysis like

=Pick(Level,

count({<Level = {2}>} TOTAL Employee),

count({<Level = {3}>} TOTAL Employee)

)

[Anonymous]

But can't I do a Count on the fly for 'Employees' where the 'Manager' = 'Employee' at the level?

So at level 1, I want in the field a count of employees where the 'Manager' is equal to the 'Employee' in Level 1:

Level 1 -> Employee = John, count of full set where 'Manager' = 'John' is 3.

Level 2 -> Employees = 'Sara', 'Marsha' and 'Jane', so count of full set where the 'Manager' = 'Sara', 'Marsha', or 'Jane' = 4?

[Anonymous]

Something like...
=count({<Employee=Manager>}DISTINCT Employee)
is what I'm going for if this is at all possible?

[swuehl]

How should that work, given the fact that set analysis won't be evaluated for each dimension line, but once per chart?

If you can modify your script, you can build a table that models your request, getting a new dimension LevelNew that links to both Employees of that level and Managers of the upper level.

Something similar to a calendar-style AsOf Table shown in

The As-Of Table

[swuehl]

something like

INPUT:

LOAD * INLINE [

    Manager, Employee, Level

    , John, 1

    John, Sara, 2

    John, Marsha, 2

    John, Jane, 2

    Sara, Simon, 3

    Sara, Ralph, 3

    Sara, Mickey, 3

    Marsh, James, 3

];

LEVELLINK:

LOAD DISTINCT Level, Level as LevelNew, 'CurrentLevel' as Type

Resident INPUT;

LOAD Level+1 as Level, Level as LevelNew, 'UpperLevel' as Type

Resident INPUT;

LevelNew	Count({<Type = {'CurrentLevel'}>}Employee)	Count({<Type = {'UpperLevel'}>}Employee)
	8	7
1	1	3
2	3	4
3	4	0

[Anonymous]

Thank you for this, and in the above situation, this is a great idea.  My example though, is just for the dimension level, I have multiple other dimensions I want to do this for, and would like to come up with a situation where I can use the same calculation in all charts.

[swuehl · Accepted solution]

You excluded the chart inter record functions like below() from your toolbox in your OP. Why so?

If you use something like

=Only(Aggr( Below(Count({<Level>}Employee)),Level))

you would even get the upper level count when filtering on Level. And it would be independent on chart column sort order (though you migh need to take care of Level load order or use the sortable aggr() function).

[Anonymous]

That is perfect, thank you.  This worked great!