Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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

'Current',

'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?

Thanks.

9 Replies

Re: Sort expression with set analysis

May be this:

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

Re: Sort expression with set analysis

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)
pandunallani
Contributor II

Re: Sort expression with set analysis

Try like this

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

singhpooja
Contributor III

Re: Sort expression with set analysis

Hi Melanie,

Are you looking for following scenario?

QVC.jpg

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,

if(AgeingBucket='Current',5)))))

Thanks,

Not applicable

Re: Sort expression with set analysis

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

Re: Sort expression with set analysis

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(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 [Aging Groups]

Re: Sort expression with set analysis

Did you try the sort expression I provided?

Not applicable

Re: Sort expression with set analysis

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.

jmmayoral3
Contributor

Re: Sort expression with set analysis

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:

SortingAgingGroups:

MAPPING

LOAD * INLINE [

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:

LOAD

.....

AgingGroup AS....

....

FROM .....

;

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

LOAD

.....

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

....

FROM .....