topic Re: User Defined Pivot Sorting in QlikView

User Defined Pivot Sorting

Anonymous — Tue, 09 Jun 2015 14:51:29 GMT

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?

Re: User Defined Pivot Sorting

swuehl — Tue, 09 Jun 2015 15:15:13 GMT

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?

Re: User Defined Pivot Sorting

Anonymous — Tue, 09 Jun 2015 15:28:11 GMT

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".

Re: User Defined Pivot Sorting

swuehl — Tue, 09 Jun 2015 15:42:22 GMT

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

...

)

Re: User Defined Pivot Sorting

Anonymous — Tue, 09 Jun 2015 16:14:40 GMT

Thanks!!!!

Re: User Defined Pivot Sorting

MarcoWedel — Tue, 09 Jun 2015 22:50:03 GMT

Hi,

another solution could be:

hope this helps

regards

Marco

Re: User Defined Pivot Sorting

Anonymous — Wed, 10 Jun 2015 17:13:16 GMT

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)

)

Re: User Defined Pivot Sorting

Wed, 10 Jun 2015 18:46:09 GMT

Can you upload an example ?

Re: User Defined Pivot Sorting

Anonymous — Wed, 10 Jun 2015 18:48:00 GMT

Sorry, against company policy.

Re: User Defined Pivot Sorting

MarcoWedel — Wed, 10 Jun 2015 20:16:01 GMT

Preparing examples for Upload - Reduction and Data Scrambling

Scrambling Data

hope this helps

regards

Marco