Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Change expression based on pivot table dimension

Hello. I am currently trying to put together a complex pivot table and I am running into a snag. Below is an example of what I am working with:pivot_table_example.PNG

I have a pivot table with District, Building, Teacher, and Student as dimensions. I want to show the average of a field at each dimension level as long as there are at least 20 students in the set, otherwise show '<20':

=if(count(DISTINCT [Students]) < 20, '<20', avg([Measure Field]))

I want this rule to apply to all dimension levels EXCEPT for student. Because when you expand down to the student level, the count will always be 1 and the measure will always show '<20'.

I want to know if there is a way that I can set my expression up to know which pivot table dimension it is currently calculating over. That way I could ignore the above rule when at the student level.

Labels (2)
1 Solution

Accepted Solutions
Partner
Partner

The aggr didn't quite work, but your suggestion gave me a great idea. I can add the TOTAL qualifier and include every dimension except [Student].

=if(count(DISTINCT TOTAL<[Districts], [Buildings], [Teachers]> [Students]) < 20, '<20', avg([Measure Field]))

I think that was what you were going for with the aggr suggestion, but for my purposes this is a little cleaner. It still has an issue when there are fewer than 20 students at the teacher level, but for what I need it works just fine.

While this solves my specific issue, it would be interesting to hear if there is a way to accomplish what I was initially asking. If there is a way to determine what dimension the expression is calculating over within the expression, but I'm pretty sure the answer is there is not.

View solution in original post

3 Replies

May be try this way?

=Sum(Aggr(if(count(DISTINCT [Students]) < 20, '<20', avg([Measure Field])),Building, Teacher)) 
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

The aggr didn't quite work, but your suggestion gave me a great idea. I can add the TOTAL qualifier and include every dimension except [Student].

=if(count(DISTINCT TOTAL<[Districts], [Buildings], [Teachers]> [Students]) < 20, '<20', avg([Measure Field]))

I think that was what you were going for with the aggr suggestion, but for my purposes this is a little cleaner. It still has an issue when there are fewer than 20 students at the teacher level, but for what I need it works just fine.

While this solves my specific issue, it would be interesting to hear if there is a way to accomplish what I was initially asking. If there is a way to determine what dimension the expression is calculating over within the expression, but I'm pretty sure the answer is there is not.

View solution in original post

Partner
Partner

After this I actually found a better way that is closer to what I was trying to do:

=if(IsNull(only([Students])),
    if(count(DISTINCT [Students]) < 20, 
        '<20', 
        avg([Measure Field])),
    avg([Measure Field])
)

Basically, it will only perform the  '<20' part of the expression when only([Students]) is null (a.k.a. when there are more than one student), otherwise it calculates normally when at the student level.