Pivot Table Decomposition

[odassier]

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 !

[odassier · Accepted solution]

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]

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]

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]

Thanks so much Olivier.