Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mee-qlik
Partner - Contributor
Partner - Contributor

Pivot Table - Sorting

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?

9 Replies
Anil_Babu_Samineni

PFA

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
mee-qlik
Partner - Contributor
Partner - Contributor
Author

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..

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
mee-qlik
Partner - Contributor
Partner - Contributor
Author

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..

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
mee-qlik
Partner - Contributor
Partner - Contributor
Author

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?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
captain89
Creator
Creator

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
Partner - Contributor
Partner - Contributor
Author

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