Sorting visual by fields difference

Q&A
, ,
1

Hi all,

I have a problem with sorting records in a bar chart.

My goal is to sort vertical bar chart by a difference between last year and this year value (from biggest positive difference/ or from biggest negative difference). To paint a picture:

  • my visual value is e.g. weight (Sum), I am grouping by Date (Year) - so I can see e.g. sum of weight this year vs last year
  • I try to sort by off-visual field which is weight difference calculated like this:
    periodOverPeriodDifference(sum({weight}), {Date}, YEAR)

This doesn’t do anything to the sorting. Can you tell me what I have been doing wrong and how should I approach this?

2

Hello @JRoz

Do you want to sort simply by the biggest amount of change regardless of positive or negative or do you want to show biggest positive difference first and largest negative difference last (or vice versa)?

I would double check that your calculation is doing something relevant by adding your weight metric, date, and period over period calculation to a table to make sure the groupings are correct. If that data looks accurate then you could try wrapping your period over period calc in a rank function, and using the rank function for the sorting instead.

3

Hi duncan!
So I want to preserve the sign and sort from biggest gain to biggest loss (or vice versa).

I tried rank. In table it works, but for some reason in bar chart no. In table I am able to sort by the rank:

image
image1924×704 84 KB

image
image1322×618 25.5 KB

In bar chart it just sorts alphabetically.

image
image1318×634 18.9 KB

Rank:

rank(
  [ maxOver(CGW diff YtD, [Fruit])
    DESC
  ],
  [ {Departure Date Loc Period} ] 
)
4

Hey @JRoz

To clarify, the bar chart didn’t let you sort by an off visual field or did the rank function not work?

5

In the table looks like the rank is working, also I could set in the bar chart sorting by an off visual field by that didn’t work - just sorted alphabetically when I set the rank as the field. However I managed to do it - no idea why it works - but it does:

weight_delta = ifelse(
  isNull(
    sumIf({weight}, Year = maxOver(Year, [], PRE_AGG)) -
    sumIf({weight}, Year = maxOver(Year, [], PRE_AGG) - 1)
  ),
  0, // Neutral value, we have to subsitute NULLs with 0 or they show first when sorting by bottom
  sumIf({weight}, Year = maxOver(Year, [], PRE_AGG)) -
  sumIf({weight}, Year = maxOver(Year, [], PRE_AGG) - 1)
)

Then I encapsulate it with rank, and add a parameter to let users sort from biggest gain/biggest loss easily:

rankFruits = ifelse(
  ${TopBottomSortDirection} = 'Sort DESC',
  rank([ {weight_delta} ASC ]),
  rank([ {weight_delta} DESC ])
)

This rank field serve me as off-visual sorting field :slight_smile: