Ordering of Chart. Dimension is a calculated column.

[Ja123__]

Hi Everyone

I'm having trouble sorting a bar chart. It's the only one on my model that isn't sorted properly.

The calculation for Salary Bracket is below. I have tried using the same expression with a if < 1000,1 etc. (Replacing the salary brackets texts below with a number to order but that method didn't seem to work.

if(

Aggr(   {< dteEffectiveDate8 = {"<=$(vEndDate)"} >}      FirstSortedValue(numAnnualSalary , - dteEffectiveDate8 ) , MemberID )

< 1000, 'Under 1K/Null',

if(

Aggr(   {< dteEffectiveDate8 = {"<=$(vEndDate)"} >}      FirstSortedValue(numAnnualSalary , - dteEffectiveDate8 ) , MemberID )

<= 50000,            '1K - 50K',

if(

Aggr(   {< dteEffectiveDate8 = {"<=$(vEndDate)"} >}      FirstSortedValue(numAnnualSalary , - dteEffectiveDate8 ) , MemberID )

<= 250000,          '50K - 250K',

if(

Aggr(   {< dteEffectiveDate8 = {"<=$(vEndDate)"} >}      FirstSortedValue(numAnnualSalary , - dteEffectiveDate8 ) , MemberID )

<= 500000,          '250K - 500K',

if(

Aggr(   {< dteEffectiveDate8 = {"<=$(vEndDate)"} >}      FirstSortedValue(numAnnualSalary , - dteEffectiveDate8 ) , MemberID )

<= 750000,          '500K - 750K',

if(

Aggr(   {< dteEffectiveDate8 = {"<=$(vEndDate)"} >}      FirstSortedValue(numAnnualSalary , - dteEffectiveDate8 ) , MemberID )

<= 1000000,       '750K - 1000K',

if(

Aggr(   {< dteEffectiveDate8 = {"<=$(vEndDate)"} >}      FirstSortedValue(numAnnualSalary , - dteEffectiveDate8 ) , MemberID )

> 1000000,          '1000K +',

'Under 1K/Null' )))))))

[JordyWegman · Accepted solution]

Hi James,

Instead of returning only '1K - 50K', you can return a dual function. This will give you the option for an extra value, on to sort on.

Dual('1K-50K',2)

Do this for all your returned values and place them in the order you want. So start with 1, then 2 and so on. Now you can sort on this value in the Sorting part.

Jordy

Climber

[Ja123__]

Thanks Jordy! Works perfectly!