- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
User Defined Pivot Sorting
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?
- Tags:
- pivot_sorting
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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',
...
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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".
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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',
...
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks!!!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
another solution could be:
hope this helps
regards
Marco
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you upload an example ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry, against company policy.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content