topic Re: Order by in a Pivot Table in QlikView

Order by in a Pivot Table

jolivares — Wed, 08 Jan 2014 12:37:18 GMT

Hi Communitty,

Below you can find a QVW attached where I was trying to order a pivot table by Date (Field = S_Date), but I failed.

Table A is working but no Table B, check that weeks are no responding to the order by. See months Dec and Sep.

Can someone see what is happening.

Re: Order by in a Pivot Table

datanibbler — Wed, 08 Jan 2014 13:03:49 GMT

Hi Juan,

I cannot tell what's the matter in September.

In Dec, though - that's a phenomenon that i guess many experienced, myself included: It's because the last days of December were effectively in week #1 (of this year). The solution is, in your master_calendar, to build a "alt_week" where you have a week #53 in that case (when the last days of Dec are in week #1 of the following year) and sort by that. You don't have to display it in the table.

HTH

Best regards,

DataNibbler

Re: Order by in a Pivot Table

bbi_mba_76 — Wed, 08 Jan 2014 13:05:59 GMT

Hi,

have you tried with =max(S_Date) to sort the week?

Remove the other marks for S_Week and set sort ascending

For each week you have more S_Date, so you have to use an aggregation function.

Re: Order by in a Pivot Table

Wed, 08 Jan 2014 13:06:33 GMT

Use Load Order "Reversed"........ and remove other check box

Selva

Re: Order by in a Pivot Table

Gysbert_Wassenaar — Wed, 08 Jan 2014 13:20:56 GMT

Replace the S_Week dimension with a calculated dimension: =dual(S_Week, WeekStart(S_Date)). Sort that ascending.

Re: Order by in a Pivot Table

preminqlik — Wed, 08 Jan 2014 13:52:37 GMT

actually for your week dimension sort conditions are more ,

go to sort tab>> uncheck all the boxes except for expression add foloowing expression

only({1}num#(S_Week))

and keep it ascending ....thats it....please find attachment below...