topic Re: Pivot Table Sort in QlikView

Pivot Table Sort

Tue, 16 Apr 2013 13:16:22 GMT

Hi, how do you sort a pivot table like this when you want the sort on the ageing categories 120 + Days, 90 - 120 days, 61 - 90 Days, 31 - 60 Days, 1 - 30 Days?

Thanks

Re: Pivot Table Sort

Gysbert_Wassenaar — Tue, 16 Apr 2013 17:10:11 GMT

That depends on how the Ageing field was created. You'll probably have to use the dual function to give it both a numeric and textual value.

If (num(Today())-Num(InvoiceDate)<31,dual(1,'1-30 Days'),

if(num(Today())-Num(InvoiceDate)<61,dual(2,'31-60 Days'),

if(num(Today())-Num(InvoiceDate)<91,dual(3,'61-90 Days'),

if(num(Today())-Num(InvoiceDate)<121,dual(4,'90-120 Days'),

dual(5,'120 + Days')))))

InvoiceDate is just a guess on my part. Replace it with something that applies to your document.

Possibly you can do this more efficiently in the script with the class statement. The class statement will create dual values automatically.

Re: Pivot Table Sort

nilesh_gangurde — Tue, 16 Apr 2013 17:18:20 GMT

Hii,

You can use the below script at load time

If (num(Today())-Num(InvoiceAge)<31,'1-30 Days',

if(num(Today())-Num(InvoiceAge)<61,'31-60 Days',

if(num(Today())-Num(InvoiceAge)<91,'61-90 Days',

if(num(Today())-Num(InvoiceAge)<121,'90-120 Days','120 + Days')))) as Field

and then in the sort tab of the pivot table put the expression to sort the column as mentioned below:

if(wildmatch( Field,'1-30 Days'),1,

if(wildmatch( Field,'31-60 Days'),2,

if(wildmatch( Field,'61-90 Days'),3,

if(wildmatch( Field,'90-120 Days'),4,5))))

-Nilesh