Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sort expression with set analysis

Hi all,

So I am having trouble sorting by a subset of Open Balance. Currently, I have these buckets listed under the field name [Aging Groups]. Aging groups determines the number of days late on open balance of clients of our company.

Aging Groups includes


'1-30 Days Late',

'31-60 Days Late',

'61-90 Days Late',

and '91+ Days Late'.

In one bar chart, I have sorted these open balances by the following expression:

Sum({<SheetName={"$(MaxSheetName)"}>}[Open Balance])

This allows me to see the client with the highest open balance to the lowest open balance where SheetName is a field created to give the Open Balance for the current date and MaxSheetName is a variable.

However, I want to see the clients descending by the order of the amount in the 91+ Days Late.

Does anyone know how I would go about doing this in the sort expressions?


9 Replies

May be this:

Sum({<SheetName={"$(MaxSheetName)"}, [Aging Groups] = {'91+ Days Late'}>}[Open Balance])


Or This

Sum({<SheetName={"$(MaxSheetName)"}, [Aging Groups] = {"$(=If(WildMatch([Aging Groups], '91+ Days Late'),[Aging Groups])))"}>} [Open Balance])

I Assume, You have the field called [Aging Groups] with the Values or else you may get those values using Aggregation. Can we get that expression for sorting

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)
Creator II
Creator II

Try like this

Sum({<SheetName={"$(MaxSheetName)"}, [Aging Groups] >= {'91+ Days Late'}>}[Open Balance])

Creator III
Creator III

Hi Melanie,

Are you looking for following scenario?


For above you can sort ageing group by giving expression like following

if(AgeingBucket='91+ Days Late',1,

if(AgeingBucket='61-90 Days Late',2,

if(AgeingBucket='31-60 Days Late',3,

if(AgeingBucket='1-30 Days Late',4,



Not applicable

Workbook .jpg

This is what I have currently. I did create the field [Aging Groups] which allows me to show the open balances like in the first chart. My set analysis expression in the original question allows me to have the stacked bar chart to the right with the clients lined up in order of the amount of open balances they have. However, I want to be able to view the clients instead by the amount of open balances they have that are 91+ days late. So in this scenario, it would be ordered Client B, A, D, C.

Not applicable

This is in the back end to create the field:

Alt(if((isNUll([Aging])),0,[Aging])) AS [Aging],
//Creates Groups for aging
//Aging =0 so an accurate average can be taken
IF(Aging>=1 and Aging <=30,'1-30 Days Late',
IF(Aging>30 and Aging <=60,'31-60 Days Late',
IF(Aging>60 and Aging<=90,'61-90 Days Late','91+ Days Late')))) AS [Aging Groups]


Did you try the sort expression I provided?

Not applicable

You're a god. I spent a majority of my yesterday on that. When I filter for 91+ days on my original client bar chart, it shows the same exact order for all of the clients who have red. However, on clients that do not have any balances within that bucket, the order seems disarray. Do you know why that would be?

Thanks so much.


Hi Melanie.

The best way to do this is using the DUAL function.

Then you can order your graph selecting Numeric Values (Ascending or descending) in the sort tab in the object properties.

Follow this procedure.

1.- Create this inline in the first tab in your script:




Name,    order

Current,                1

1-30 Days Late,     2

31-60 Days Late,   3

61-90 Days Late,   4

91+ Days Late,     5


I don't know the name of the field which contains the values 'Current', 1-30 Days...... etc.

I am going to call it AgingGroup. You have to change it for the correct name.

2. In the script, where you LOAD the field AgingGroup:



AgingGroup AS....


FROM .....


You have to change the name of the field for a Dual function. Your LOAD Script looks like this:



DUAL(AgingGroup, APPLYMAP('SortingAgingGroups',AgingGroup)) AS AgingGroup,


FROM .....