Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Not applicable

Using Multiple Sort Expressions

Hi All,

I am currently using the following expression

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


to sort open balances by amount of 'red dollars'.


As background, I have 5 buckets that fall under the field [AgingGroups]:


IF(isNUll([Aging]),'Current',
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 [AgingGroups]

Where [Aging] is just a column in an excel sheet that displays the number of days the balance is past due.

These buckets are displayed on the front end as colors: dark green, light green, yellow, orange, and red.

Currently, I have sorted the open balances by most red dollars to least red dollars with the expression above (Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'91+ Days Late'}>}[Open Balance]) ). However, once there are zero red dollars, the open balances are sorted in alphabetical order by client. I want it to sort by red dollars, and then if there are no red dollars, sort by orange dollars, etc, all the way until the dark green.

How would I go about doing this in my Sort Expression? Am I allowed to sort by multiple expressions? Simply typing

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

+

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'61-90 Days Late'}>}[Open Balance])

+

...

does not work.

Suggestions?

Thank you,

Melanie

1 Solution

Accepted Solutions
Highlighted

Re: Using Multiple Sort Expressions

You can try like this:

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

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'61-90 Days Late'}>}[Open Balance])/1E4 +

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'31-60 Days Late'}>}[Open Balance])/1E8 +

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'1-30 Days Late'}>}[Open Balance])/1E12 +

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'Current'}>}[Open Balance])/1E12

Basically giving more weight to first expression and giving lessor weight to other expressions

View solution in original post

5 Replies
Highlighted

Re: Using Multiple Sort Expressions

You can try like this:

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

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'61-90 Days Late'}>}[Open Balance])/1E4 +

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'31-60 Days Late'}>}[Open Balance])/1E8 +

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'1-30 Days Late'}>}[Open Balance])/1E12 +

Sum({<SheetName={"$(MaxSheetName)"}, AgingGroups = {'Current'}>}[Open Balance])/1E12

Basically giving more weight to first expression and giving lessor weight to other expressions

View solution in original post

Highlighted
Not applicable

Re: Using Multiple Sort Expressions

This worked. But can you explain why? I was trying to do a multiplier. So the same concept with the weighted values where I multiplied by either 1000, 100, 10, 1, or 0. And how does this work without messing up the Open Balance values?

Highlighted

Re: Using Multiple Sort Expressions

Should have worked the same way with your multiplication depending on the values of the above expressions. I just added some extra weighting by dividing by 10000, 100000000, 1000000000000, 10000000000000000.

I just noticed that I had 1E12, may be change that to 1E16 or 1E14 (not sure if 1E16 can be used)

Highlighted
Not applicable

Re: Using Multiple Sort Expressions

I did change it to 1E16, and it worked, thanks. Maybe my multipliers weren't spread out enough so the total dollars still allowed open balances with high orange values to pass the red, etc.

Highlighted

Re: Using Multiple Sort Expressions

Ya, that's what I am thinking as well. But I am glad it worked out well in the end