Pivot Table Sort

[#1]

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

[Gysbert_Wassenaar]

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.

[nilesh_gangurde]

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