Sort expression with set analysis

[#1]

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.

[sunny_talwar]

May be this:

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

[Anil_Babu_Samineni]

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

[Anonymous]

Try like this

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

[Anonymous]

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,

[#6]

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.

[#7]

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]

[sunny_talwar]

Did you try the sort expression I provided?

[#9]

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]

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