Hi all,
I have a dashboard that has various time selections (YTD, YTG, Fiscal Year,, QTR etc). I am trying to sort the dimension based on the time selection selected. eg.
Dimension = Period (01, 02, 03, 04 etc up to 13)
The Period dimension is pivoted along the top and the expressions are pivoted down the left side.
The expressions span over multiple years, i.e
Sales TY
Sales LY
etc
I am looking for the sorting to have the following rules..
Selections made = Year=2019, Period = 9
| Time Selection | Rule | Example |
| QTR | sorting asc (current QTR that selected period falls in) | 08, 09, 10 |
| YTD | sorting asc (inc the selected period ) | 01, 02, 03, 04, 05, 06, 07, 08, 09 |
| YTG | sorting asc (from the selected period) | 09, 10, 11, 12, 13 |
| Next 13 Periods | sorting asc | 09, 10, 11, 12,13, 01, 02, 03, 04, 05, 06, 07, 08 |
| Prev 13 Periods | Sorting desc (starting at the period 13 periods back from selected period), so if Period 9 is selected then it would be selected - 12 which would be period 10 of the previous year) | 10, 11, 12, 13, 01, 02, 03, 04, 05, 06, 07, 08, 09. |
| Selected Period | | 09 |
| Fiscal Year | sorting Asc | 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13 |
Currently the sorting works for all of the above except Prev 13 Periods and i cannot get it to work.
I have tried creating an inline load to use for sorting of the Prev 13 Periods selection only just to test it but that doesn't seem to be working either.
I believe the issue is to do with the periods spanning over 2 years, i do have YearPeriod fields but i can't use them as the dimension as it will double the amount of columns and the Sales TY ans Sales LY will be displayed in 2 separate columns. I have tried sorting on the YearPeriod field but it doesn't work.
I also have counter fields which count the yearperiods, i then use them to get the correct YearPeriod value needed for selected - 12 , I am currently using them in the sort expression but it doesn't work for the 'Prev 13 Periods' time selection.
Just for info, the 'Prev 13 periods' sorting is only very slightly out. It is displaying the selected period in the 1st column whereas i would want that in the last column.
Can anyone please help with this, i have attached an example QVW.
