topic Re: Sort expression with set analysis in QlikView

Sort expression with set analysis

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Sort expression with set analysis

sunny_talwar — Wed, 30 Nov 2016 22:14:31 GMT

May be this:

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

Re: Sort expression with set analysis

Anil_Babu_Samineni — Thu, 01 Dec 2016 02:37:59 GMT

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

Re: Sort expression with set analysis

Anonymous — Thu, 01 Dec 2016 03:55:54 GMT

Try like this

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

Re: Sort expression with set analysis

Anonymous — Thu, 01 Dec 2016 05:35:16 GMT

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,

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

Thanks,

Re: Sort expression with set analysis

Thu, 01 Dec 2016 16:26:14 GMT

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.

Re: Sort expression with set analysis

Thu, 01 Dec 2016 16:30:12 GMT

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

sunny_talwar — Thu, 01 Dec 2016 16:46:30 GMT

Did you try the sort expression I provided?

Re: Sort expression with set analysis

Thu, 01 Dec 2016 17:06:40 GMT

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.

Re: Sort expression with set analysis

jmmayoral3 — Mon, 30 Jan 2017 14:57:21 GMT

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 .....