Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Related Maximum Value in Calculated Dimension

I'm trying to add a calculated dimension to a pivot table I built a few months back.  The dimension needs to show the last approver in the sequence (or approvers if there is more than one approver with the same order number) while also being sensitive to selection.  I've tried a number of different aggregations and explored using set analysis but have been unsuccessful for the most part. I've attached a sample app that includes my latest dimesnion but this still isn't returning the correct results.  I've also included some samples of expected outputs based on selections in the below table.

Approvers Selected
Names Displayed in Dim
None5- Todd    20- Bill,Tony
Bill, Joe, John5- Bill    20- Bill
Harry, John, Todd, Tony5- Todd    20- Tony
1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like attached?

View solution in original post

4 Replies
swuehl
MVP
MVP

Maybe like attached?

Anonymous
Not applicable
Author

This is perfect! Thank you so much.  Would you mind giving some background on what your thought process was behind the expression?  It would be really helpful to understand for my own purposes (as well as others on the forum).  Thank you again!

swuehl
MVP
MVP

If you want to create a dimension you can either use a plain field or a calculcated dimension based on a field, e.g.

=FIELD

=if(FIELD > 10, FIELD)

For more complex tasks, you can use advanced aggregation, which essentially produces again a one dimensional vector as dimension, one value per combination of advanced aggregation dimension values.

[Another, different option to create a dimension is a synthetic dimension using Valuelist() / Valueloop]

You want to implement something more complex than just a (filtered) FIELD, so let's start with advanced aggregation:

aggr( "EXP", "DIMs")

Your DIMs should be ID, since you want a value per ID.

aggr( "EXP", ID)

At this point, I sometimes create a straight table chart with dimension ID (in this case) and play around with the expression until I get what I want. We want "last approver in the sequence", and if I read something like this, I go for the FirstSortedValue() function.

Sequence mean sorted by Order per ID, and approver equals Name, so maybe something like

=FirstSortedValue( Name, -Order)

This will not handle the multiple approvers with same order number correctly, so we need to add an aggregation (and an aggregation within an aggregation like FirstSortedValue requires advanced aggregation again:

=FirstSortedValue( aggr(concat(Name,', '), ID, Order), -aggr( Order, ID, Order) )

and putting all together in one expression for the calculated dimension:

=Aggr(FirstSortedValue( aggr(concat(Name,', '), ID, Order), -aggr( Order, ID, Order)),ID )

Regards,

Stefan

Anonymous
Not applicable
Author

This is great, Stefan! Thank you so much for this.  This is extremely helpful and signficantly improves my understanding of calculated dimensions.