User Defined Pivot Sorting

[Anonymous]

I have a pivot chart that displays 12 months of sales data, with the months in columns and sorted according to our fiscal year (Sept through Aug). However, the users want to be able to dynamically change it so that any month can be the "first" month so that when they print or export the months line up with whatever the selected customer's fiscal year is. Has anyone ever done something like this?

[swuehl]

Not sure if I understand, could you post a sample QVW?

I think sorting by a monthstart date would always show the correct chronologic order for the FY months available.

If they have a fiscal year Jan to Dec and resort your table, wouldn't they sort Jan 2016 before September 2015 then?

[Anonymous]

In our master calendar we have a field called Fiscal Month Number. It has 1 assigned to Sept, 2 to Oct, 12 to Aug. That field is used to sort the months in the pivot. But for some customers, Oct is their first fiscal month. Or April or January. So the users what to be able to use an input box where they can designate the month to use as month "1".

[swuehl · Accepted solution]

Use a sort expression for the dimension

=match([Fiscal Month Number], $(vSortList) )

Then the user can basically define a comma separated list in variable vSortList input box to set the sort order

vSortList = 5,6,7,8,9,10,11,12,1,2,3,4

You can only let select the first digit in variable vFirstMonth, then fill the list with some logic, maybe like this as vSortList variable expression

=pick(vFirstMonth,

'5,6,7,8,9,10,11,12,1,2,3,4',

'6,7,8,9,10,11,12,1,2,3,4,5',

'7,8,9,10,11,12,1,2,3,4,5,6',

...

)

[Anonymous]

Thanks!!!!

[MarcoWedel]

Hi,

another solution could be:

hope this helps

regards

Marco

[Anonymous]

My final solution. Probably not the best but it seems to work fine.

vFirstMonth is a variable (1 -12) the user can set using the InputBox at the bottom.

Sort Expression: 

=If([Calendar Month Number]-(vFirstMonth-1)>0,[Calendar Month Number]-(vFirstMonth-1),

    If([Calendar Month Number]-(vFirstMonth-1)<=0,[Calendar Month Number]-(vFirstMonth-1)+12) 

)

[#8]

Can you upload an example ?

[Anonymous]

Sorry, against company policy.

[MarcoWedel]

Preparing examples for Upload - Reduction and Data Scrambling

Scrambling Data

Scrambling Data

hope this helps

regards

Marco