Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis - Values outside Dimension in Straight Table

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

8 Replies
swuehl
MVP
MVP

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
Not applicable
Author

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
Not applicable
Author

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

swuehl
MVP
MVP

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
MVP
MVP

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
113
234
340
Anonymous
Not applicable
Author

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
MVP
MVP

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
Not applicable
Author

That is perfect, thank you.  This worked great!