Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Group dimension in Pivot table

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

4 Replies
Highlighted

Re: Group dimension in Pivot table

Please enable "Partial Sum" for GMM

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Master II
Master II

Re: Group dimension in Pivot table

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

];

Capture.JPG

Highlighted

Re: Group dimension in Pivot table

You have nice patience, Great !!!

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor III
Contributor III

Re: Group dimension in Pivot table

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