topic Re: Group dimension in Pivot table in QlikView

Group dimension in Pivot table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I would like to get some help with QlikView please.

I have a pivot table and I would like to create another sub group from custody under product family heading so that it reads escrow to cover the escrow east and escrow west values with its own total.

Please see screenshot from a sample of the pivot table.

Kind regards,

Esther

Re: Group dimension in Pivot table

Anil_Babu_Samineni — Thu, 26 Oct 2017 01:43:10 GMT

Please enable "Partial Sum" for GMM

Re: Group dimension in Pivot table

devarasu07 — Thu, 26 Oct 2017 01:55:04 GMT

Hi,

going forward always try to share source as text/excel and expected out that would be easy for us to check and help you quickly

Try to created calculated sub group dimension at back end using preceding load and in the front end - pivot chart property -- enabled partial sum for new group field ([Product Family Group], Product Remap and third dimension )

Data:

load *,

if([Product Family]='Custody' and ([Product Remap] ='Escrow East' or [Product Remap] ='Escrow West'),'Escrow',[Product Family]) as [Product Family Group];

LOAD * INLINE [

Product Family, Product Remap, Type, Sales

Banking, Banking, GMM, 90

Banking, Banking, Canada, 100

Banking, Banking, Central, 200

Banking, Banking, Eastern, 340

Banking, Banking, HNW, 500

Banking, Banking, LatAm, 300

Banking, Banking, LFG, 500

Banking, Banking, NAM, 300

Banking, Banking, Metro NY, 250

Banking, Banking, Western, 100

Custody, Custody, Central, 200

Custody, Custody, Eastern, 340

Custody, Custody, HNW, 500

Custody, Custody, LatAm, 300

Custody, Custody, LFG, 500

Custody, Custody, Metro NY, 300

Custody, Custody, NAM, 250

Custody, Custody, Western, 417

Custody, Escrow East, Eastern, 250

Custody, Escrow East, LFG, 208

Custody, Escrow East, Metro NY, 83

Custody, Escrow West, Central, 167

Custody, Escrow West, LFG, 283

];

Re: Group dimension in Pivot table

Anil_Babu_Samineni — Thu, 26 Oct 2017 02:18:15 GMT

You have nice patience, Great !!!

Re: Group dimension in Pivot table

Anonymous — Thu, 26 Oct 2017 18:32:02 GMT

Hi,

Thank you for your help,

I selected the edit group function in the dimensions field and added the above expression. It works but Escrow and custody are reversed in the product remap field like the example below.

Is there any way that I can switch the two around to match the results that you have without reloading the data?

Kind regards,

Esther