Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
odassier
Creator II
Creator II

Pivot Table Decomposition

Hello all,

I work in risk management where most of the data (i.e. Risk) is not additive, so we use group-by in excel to aggregate individual sources of risk into a portfolio risk summary report.  I've been trying to replicate the table format in Qlik using Pivot Table but with no success so far.

I'm attaching an excel workbook with two sheets.  The first ("Source") is how we report this summary in excel (so you can see what I'm trying to replicate in Qlik).  The second sheet ("QlikDataModel") is my last try at building the data model for Qlik's Pivot table.  I'm not sure it is right but I've tried a lot of others and none have worked so far.  Right now there is only one dummy portfolio for one date in there but if I can get it to work then I'll add (much) more data. 

I'm also attaching the Qlik app where I tried to make it work so you can see the problem I"m having (in case I'm just not using the Qlik Pivot table right).

Any help appreciated.

Olivier !

1 Solution

Accepted Solutions
odassier
Creator II
Creator II
Author

Actually, I answered my own (dumb) question here.  By using asset class and sub asset class as dimensions and the Pivot Table, I was able to get what I wanted.

View solution in original post

4 Replies
odassier
Creator II
Creator II
Author

Actually, I answered my own (dumb) question here.  By using asset class and sub asset class as dimensions and the Pivot Table, I was able to get what I wanted.

Anonymous
Not applicable

Hi Olivier.

Could you please elaborate a little bit more on how you solved your issue? I think I may need something similar and it would be a great help if you could attach your final .qvf to understand what you did.

Thanks in advance.

odassier
Creator II
Creator II
Author

Hi Alejandro,

What I did was basically to create dimension columns for the various levels I wanted in the risk decomposition (see attached excel and picture of final pivot table in Qlik Sense). In the attached spreadsheet, Portfolio, Asset Class, and Holdings columns are dimensions I can use in the pivot table to create a drilldown decomposition of the portfolio risk along those dimensions. You can create other columns for the hierarchy that you would like to use in the breakdown, such as Country, Sectors, Industries, etc.

I hope this helps.

Olivier

Anonymous
Not applicable

Thanks so much Olivier.