sorting Pivot based on period

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a problem where i need to sort a pivot table based on the dimension (pivoted) but based on a time selection.

i.e

The user has the following time selectiomns that they can choose from:

Selected Period

YTD

YTG

Fiscal Year

QTR

Last 13 Periods

Next 13 Periods

the Period field is pivoted along the top and the expressions are pivoted down the left side.

The sorting of the Periods work for all time selections apart from the last 2 in the list above.

e.g.

If period 9 is selected and the user selected YTG (Year to Go) then the sorting of the Periods are :

which is correct

but if the user selected Period 9 and 'Next 13 Periods', i want the sorting to be:

obviously from period 01 onwards would be the next year.

And if they selected Period 9 and Prev 13 Periods, it should be sorted

etc

Can anyone help please?

I have attached a sample.

Re: sorting Pivot based on period

hopkinsc — Tue, 05 Mar 2019 10:19:25 GMT

Can anyone help with this please? 🙂

rwunderlich — Tue, 05 Mar 2019 16:18:06 GMT

In general you can sort on a mid-point (split) using a sort expression like this:

if(Period<split, Period + 100, Period)

-Rob