Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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
sunny_talwar

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

Not applicable
Author

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?

sunny_talwar

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)

Not applicable
Author

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.

sunny_talwar

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