Pivot Table Percentages

[rwb139]

Hi Qlik Community,

I am trying to create a pivot table that has the following layout where I have a list of our partners on as rows, and as subdimensions of those it would have what properties they have invested in, for how much, and what percentage ownership it is. It works when showing only one property. For example if Property 1 costs $244,134.04 and partner 1 owned all of that amount it shows up as % ownership = 100%. However, when I add more properties, it instead takes the total amount of all the properties selected and throws off the calculation. Hoping someone can help figure out this issue. I have a picture of the calculation that I am using for the % ownership as well.

[GaryGiles · Accepted solution]

Not sure if I have the field names for Property and Owner correct, but I think this is what need:

sum({<gl_account_type={'Capital'}>} Total <[Property Name],Owner>
if(wildmatch(gl_account_name,'*Initial*'), jan)* -1)
/
sum({<gl_account_type={'Capital'}>} Total <[Property Name]>
if(wildmatch(gl_account_name,'*Initial*'), jan)* -1)

[rwb139]

Hi @GaryGiles 

Thanks for your help! I do have a quick question as I am pretty new to this: should I make a calculated field named "Owner" that is: if(gl_account_type='Capital',if(WildMatch(gl_account_name, '*Initial*'),gl_account_name))? Because right now I don't have an "Owner" field and when I use that formula in it's place I get an error. Thanks again for your time!

[GaryGiles]

Yes, you will need to create an owner field in your load script since you can't reference a formula using Total <fieldname>.  It needs to be a field.  Hope this helps.

[rwb139]

@GaryGiles 

Thanks for your help. I've been working on this today to see if I can't get it to work exactly how I'd like. I've modified your formula and it's now giving me the distribution of how each partners money is which will be very helpful (50% to property 1, 50% to property 2, etc). However, I am also trying to get it (and maybe this will need to be a new table) to where it shows the percentage ownership of each individual property (property 1 is owned 20% by partner 1, 20% by partner 2, etc). I've added a picture and my 'Ownershipt %' formula to help explain what I'd like. Thanks again for your help and let me know if that's not clear.  

sum({<gl_account_type={'Capital'}>}
if(wildmatch(gl_account_name,'*Initial*'), jan)* -1)
/
sum({<gl_account_type={'Capital'}>} Total <[Property Name], initial_partners>
if(wildmatch(gl_account_name,'*Initial*'), jan)* -1)

[GaryGiles]

I'm not sure what the problem might be.  Back to my original post, this expression should give you the ownership percent for each owner for each property.  Just to be clear, teh initial_partners only has one partner per row.  Correct?

sum({<gl_account_type={'Capital'}>} Total <[Property Name], initial_partners>
if(wildmatch(gl_account_name,'*Initial*'), jan)* -1)
/
sum({<gl_account_type={'Capital'}>} Total <[Property Name]>
if(wildmatch(gl_account_name,'*Initial*'), jan)* -1)