Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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',

...

)

View solution in original post

9 Replies
swuehl
MVP
MVP

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
Not applicable
Author

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
MVP
MVP

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
Not applicable
Author

Thanks!!!!

MarcoWedel

Hi,

another solution could be:

QlikCommunity_Thread_167626_Pic1.JPG

QlikCommunity_Thread_167626_Pic2.JPG

QlikCommunity_Thread_167626_Pic3.JPG

QlikCommunity_Thread_167626_Pic4.JPG

hope this helps

regards

Marco

Anonymous
Not applicable
Author

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) 

)

2015-06-10_111044.jpg

Not applicable
Author

Can you upload an example ?

Anonymous
Not applicable
Author

Sorry, against company policy.