Sort Quarter not in a traditional way

[stiankar]

Hi, I need to sort quarters in an unconventional way. I have an expression that works for months, but the quarters don't behave as wanted. 

Let's say you select 2021 and Q2, i then want the sorting to be 

Q3, Q4, Q1 and Q2

Since for the months 

It goes form juli to june  when I select 2021 and Q2. 

My expression is this: 

$(vExprRull12.MonthsAgo.Regnskapslinje.Start.End.Sorting(0,EBIT,-0,-11))

let vExprRull12.MonthsAgo.Regnskapslinje.Start.End.Sorting = '
only({<[Months ago]={"<=$1"},Regnskapslinje={"$2"},%DateKey={"<=$'&'(=date(monthend(addmonths(max(%DateKey),$3)))) >=$'&'(=date(monthstart(addmonths(max(%DateKey),$4))))"},[AsOf Month]=,[AsOf Year]=, [AsOf Kvartal]=>}num([AsOf Måned-År]))
';

PS: I use an AsOf calendar in order to make some rolling avgs and other stuff. 

[JordyWegman]

Hi,

I think the easiest way is just to create a table that adds your custom sorting.

Load * Inline [
Quarter, _indQuarterSort
Q3, 1
Q1, 2
Q4, 3
Q2, 4
];

Left join this to your calendar and add the sorting field in the  chart you need.

Do the same for your months or even do it on day level.

Jordy

Climber

[stevejoyce]

I think he's looking for it to be dynamic based on selections.

how about this as your sort expression, sort DESCENDING.

you may want to ignore fields explicitly in the first max function, probably similar to your set analysis of your expression, i only did {1} to make it simpler and ignore all selections.

Assuming Quarter is the name of the field in your visual.  It will look at the max Quarter in your selections for your "As of Quarter".  It will not match where Quarter = max(Quarter) and return 0, that's why sort descending.

match(max({1} RIGHT(Quarter,1)), mod($(=max(right(Quarter,1))),4)+3, mod($(=max(right(Quarter,1))),4)+2,mod($(=max(right(Quarter,1))),4)+1)

[stiankar]

That will not work. It needs to be dynamic, so if I select Q3 i need the sorting to be

Q4, Q1, Q2 and Q3

And as mentioned this work with months. If I select Q3 they will start at

Oct and end on Sep

So you can’t predetermin the sort order, it needs to be an expression.

[stiankar]

That was very close.

And yes it needs to be dynamic based on selections 🙂 

[stevejoyce]

What scenario is it not working for?

[stiankar]

Look at the picture, and you’ll see that is does not sort correctly. I have selected q2, but q2 is not the last quarter in the graph

[stevejoyce · Accepted solution]

You're right.  I like this calculation better. I'm just adding 10 as an arbitrary number > 4.

Sort this ascending.  This will just sort on the quarter number, and if it is less then the selected (or max available) quarter then add 10.

If(max({1} RIGHT(Quarter,1)) <= $(=max(right(Quarter,1))), 10, 0) + max({1} RIGHT(Quarter,1))

[stiankar]

I think you are on to something there 😁

Thanks alot, man I love this community. Almost the best part of Qlik.