topic Re: Sorting In Pivot Table in QlikView

Sorting In Pivot Table

neha_shirsath — Thu, 22 Aug 2013 09:55:08 GMT

Hello Community,

I have pivot table like-

	Month-Year	OCT-13		SEP-13		AUG-13		NOV-13		DEC-13		NOV-15
Organisation	Code	Amount Remaining	Amount Original	Amount Remaining	Amount Original	Amount Remaining	Amount Original	Amount Remaining	Amount Original	Amount Remaining	Amount Original	Amount Remaining	Amount Original
A	USD	2556	2556	7661	7661	-	-	-	-	-	-	-	-
B	VSD	116225	116225	892913	892916	138132	138932	20036	20036	-	-	-	-
C	MS	22085	22085	60013	60013	22608	22608	6813	6813	-	-	-	-
D	TS	-	-	-	-	-	-	-	-	-	-	-	-

I want the sorting on Month-Year field like

SEP,OCT,NOV,DEC...etc.

Like this-

	Month-Year	AUG-13		SEP-13		OCt-13		NOV-13		DEC-13		NOV-15
Organisation	Code	Amount Remaining	Amount Original	Amount Remaining	Amount Original	Amount Remaining	Amount Original	Amount Remaining	Amount Original	Amount Remaining	Amount Original	Amount Remaining	Amount Original
A	USD	2556	2556	7661	7661	-	-	-	-	-	-	-	-
B	VSD	116225	116225	892913	892916	138132	138932	20036	20036	-	-	-	-
C	MS	22085	22085	60013	60013	22608	22608	6813	6813	-	-	-	-
D	TS	-	-	-	-	-	-	-	-	-	-	-	-

-Thanks & Regards,

Neha

Re: Sorting In Pivot Table

Thu, 22 Aug 2013 09:58:40 GMT

use sort number and sort on date

or make in the script year&num(Month,'00')

Re: Sorting In Pivot Table

sujeetsingh — Thu, 22 Aug 2013 10:02:20 GMT

Select sorting by expression default

Re: Sorting In Pivot Table

cesaraccardi — Thu, 22 Aug 2013 10:05:02 GMT

Hi Neha,

In order to do that you need to "tell" QlikView that these values should be interpreted as Dates (numbers). I suggest you to do the following:

1. Change the variable MonthNames in your script to UPPER CASE values:

SET MonthNames='JAN;FEB;MAR;APR;MAY;JUN;JUL;AUG;SEP;OCT;NOV;DEC';

2. When loading the field Month-Year use this syntax:

LOAD

(...),

Date#(Month-Year,'MMM-YY') as Month-Year

FROM

TABLE;

3. Change the table to sort the field numerically.

Regards,

Cesar

Re: Sorting In Pivot Table

neha_shirsath — Thu, 22 Aug 2013 10:06:35 GMT

Hi Sujeet,

Thanks for quick reply but it's not working with expression default.

Re: Sorting In Pivot Table

neha_shirsath — Thu, 22 Aug 2013 10:17:58 GMT

Hello Cesar,

Not working yet ...

Re: Sorting In Pivot Table

sujeetsingh — Thu, 22 Aug 2013 10:21:37 GMT

If possible can you please give a sample .

Re: Sorting In Pivot Table

sandeepprasad_j — Thu, 22 Aug 2013 10:27:46 GMT

Hi Neha ,

In Sort tab Select Load Order as 'Reversed'

Hope this helps

Thanks,

Sandeep.

Re: Sorting In Pivot Table

cesaraccardi — Thu, 22 Aug 2013 10:34:53 GMT

Hi Neha,

It should work, also make sure you have only one criteria selected.

Cesar

Re: Re: Sorting In Pivot Table

cesaraccardi — Thu, 22 Aug 2013 10:43:00 GMT

Hi Neha,

Please have a look at the attachment.

Kind Regards,

Cesar