Pivot Table - Sorting

[mee-qlik]

Hi,

I have some financial data with a hierarchy on “cod voce” dimension ( see “Detail” tab in the example file).

I drew a list box with option “Show as treeView” and sorting by “cod voce” . Works perfectly.

Then I drew a pivot table with the same sorting, but sorting don’t work ( see attached file) even if I use the same sorting criteria.

For example see the position of “Total” in the first picture (correct) and in the second picture (wrong).

I tried same other criteria, unsuccessfully.

Can you help me?

[Anil_Babu_Samineni]

PFA

[mee-qlik]

Thank you Anil,

but with your solution only the "total" go to the right position, but other record no

My goal in pivot table is to respect the order as in the first picture (treeview)

[Anil_Babu_Samineni]

Check Now? I prefer to work same sequential order in script instead UI to make dynamic..

[mee-qlik]

Thank you again Anil.

Your function (match) work only in this particulary case.

But goal is sort ALL the data by "cod voce" or, if you prefer numerico dimension in sequential order, "num_riga_saldi_cee".

I try even with this dimension, but it don't work.

[Anil_Babu_Samineni]

I didn't say this will work fully, but partially it should work. And that is why i commented to manipulate in script the same order..

The reason i am saying is either it will work A-Z string pattern and number format.. if you need certain order one option like mine earlier and another one is script..

[mee-qlik]

Thank you again Anil,

but in the script, if you prefer to work with a progressive number, is already present a dimension numeric progressive as you can see in tab "detail". I try to use it sorting the pivot table but it doesn' t work.

[Anil_Babu_Samineni]

I have attached which can be done, Is that  issue still persist?

[captain89]

Hi Enrico,

I solved the same problem with sql.

In this way you get the right total in the right row:

SELECT                                                     

A.cod_bilcee_mod AS cod_bilcee_mod,                                       

A.cod_voce AS cod_voce,                                       

A.cod_voce AS cod_voce_padre                                        

       FROM [CA_BC_VOCI] A

UNION 

SELECT

A.cod_bilcee_mod AS cod_bilcee_mod,                                       

A.cod_voce AS cod_voce,                                       

A.cod_voce_padre AS cod_voce_padre                                        

       FROM [CA_BC_VOCI] A                                 

UNION                                                    

SELECT                                                    

A.cod_bilcee_mod AS cod_bilcee_mod,                                       

A.cod_voce AS cod_voce,                                       

B.cod_voce_padre AS cod_voce_padre                                        

      FROM [CA_BC_VOCI] A                                    

      LEFT JOIN [CA_BC_VOCI] B ON B.cod_bilcee_mod=A.cod_bilcee_mod AND B.cod_voce=A.cod_voce_padre                                          

      WHERE B.cod_voce_padre<>0

UNION                                                    

SELECT                                                    

A.cod_bilcee_mod AS cod_bilcee_mod,                                       

A.cod_voce AS cod_voce,                                       

C.cod_voce_padre AS cod_voce_padre                                        

      FROM [CA_BC_VOCI] A                                    

      LEFT JOIN [CA_BC_VOCI] B ON B.cod_bilcee_mod=A.cod_bilcee_mod AND B.cod_voce=A.cod_voce_padre  

      LEFT JOIN [CA_BC_VOCI] C ON C.cod_bilcee_mod=B.cod_bilcee_mod AND C.cod_voce=B.cod_voce_padre                                         

      WHERE C.cod_voce_padre<>0

UNION                                                    

SELECT                                                    

A.cod_bilcee_mod AS cod_bilcee_mod,                                       

A.cod_voce AS cod_voce,                                       

D.cod_voce_padre AS cod_voce_padre                                        

      FROM [CA_BC_VOCI] A                                    

      LEFT JOIN [CA_BC_VOCI] B ON B.cod_bilcee_mod=A.cod_bilcee_mod AND B.cod_voce=A.cod_voce_padre  

      LEFT JOIN [CA_BC_VOCI] C ON C.cod_bilcee_mod=B.cod_bilcee_mod AND C.cod_voce=B.cod_voce_padre 

      LEFT JOIN [CA_BC_VOCI] D ON D.cod_bilcee_mod=C.cod_bilcee_mod AND D.cod_voce=C.cod_voce_padre                                        

      WHERE D.cod_voce_padre<>0

UNION                                                    

SELECT                                                    

A.cod_bilcee_mod AS cod_bilcee_mod,                                       

A.cod_voce AS cod_voce,                                       

E.cod_voce_padre AS cod_voce_padre                                        

      FROM [CA_BC_VOCI] A                                    

      LEFT JOIN [CA_BC_VOCI] B ON B.cod_bilcee_mod=A.cod_bilcee_mod AND B.cod_voce=A.cod_voce_padre  

      LEFT JOIN [CA_BC_VOCI] C ON C.cod_bilcee_mod=B.cod_bilcee_mod AND C.cod_voce=B.cod_voce_padre 

      LEFT JOIN [CA_BC_VOCI] D ON D.cod_bilcee_mod=C.cod_bilcee_mod AND D.cod_voce=C.cod_voce_padre

      LEFT JOIN [CA_BC_VOCI] E ON D.cod_bilcee_mod=D.cod_bilcee_mod AND E.cod_voce=D.cod_voce_padre                                        

      WHERE E.cod_voce_padre<>0

[mee-qlik]

Hi Matteo,

thank you for your time. The problem isn't the totals ( the DB table of the financial program is done like an old cobol program, ther are the rows of detail and even the rows of totals)

My only problem is sorting the pivot table that i drew. My goal is sort the pivot table by "cod_voce" or "num_riga", how you prefer.

Thank you again