topic Trouble allocating in a pivot table in QlikView https://community.qlik.com/t5/QlikView/Trouble-allocating-in-a-pivot-table/m-p/410461#M152751 <HTML><HEAD></HEAD><BODY><P>I'm trying to create a pivot table with the following dimensions:</P><P>State</P><P>County</P><P>Contract #</P><P></P><P>It will have a few expressions. One is Losses which is simply sum(Loss).</P><P></P><P>In the data, the losses are split out by the state and county, but the premium isn't. I want to allocate the premium based on the Loss/Total Loss ratio. So if I have the following:</P><P></P><P>Contract&nbsp;&nbsp;&nbsp;&nbsp; State&nbsp;&nbsp;&nbsp;&nbsp; County&nbsp;&nbsp;&nbsp;&nbsp; Loss</P><P>A1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3000</P><P>A1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7000</P><P></P><P>and the contract has a premium of 5000 (that's stored in another table), I want the following pivot table:</P><P></P><P>State&nbsp;&nbsp;&nbsp;&nbsp; County&nbsp;&nbsp;&nbsp;&nbsp; Loss&nbsp;&nbsp;&nbsp;&nbsp; Premium</P><P>NY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3000&nbsp;&nbsp;&nbsp;&nbsp; 1500</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7000&nbsp;&nbsp;&nbsp;&nbsp; 3500</P><P></P><P>I have it working when the table is fully expanded. But when I collapse it to show totals by State, the numbers completely change.</P><P></P><P>Any idea on a formula to allocate the premium properly?</P></BODY></HTML> Wed, 09 Jan 2013 20:44:19 GMT danielact 2013-01-09T20:44:19Z Trouble allocating in a pivot table https://community.qlik.com/t5/QlikView/Trouble-allocating-in-a-pivot-table/m-p/410461#M152751 <HTML><HEAD></HEAD><BODY><P>I'm trying to create a pivot table with the following dimensions:</P><P>State</P><P>County</P><P>Contract #</P><P></P><P>It will have a few expressions. One is Losses which is simply sum(Loss).</P><P></P><P>In the data, the losses are split out by the state and county, but the premium isn't. I want to allocate the premium based on the Loss/Total Loss ratio. So if I have the following:</P><P></P><P>Contract&nbsp;&nbsp;&nbsp;&nbsp; State&nbsp;&nbsp;&nbsp;&nbsp; County&nbsp;&nbsp;&nbsp;&nbsp; Loss</P><P>A1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3000</P><P>A1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7000</P><P></P><P>and the contract has a premium of 5000 (that's stored in another table), I want the following pivot table:</P><P></P><P>State&nbsp;&nbsp;&nbsp;&nbsp; County&nbsp;&nbsp;&nbsp;&nbsp; Loss&nbsp;&nbsp;&nbsp;&nbsp; Premium</P><P>NY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3000&nbsp;&nbsp;&nbsp;&nbsp; 1500</P><P>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7000&nbsp;&nbsp;&nbsp;&nbsp; 3500</P><P></P><P>I have it working when the table is fully expanded. But when I collapse it to show totals by State, the numbers completely change.</P><P></P><P>Any idea on a formula to allocate the premium properly?</P></BODY></HTML> Wed, 09 Jan 2013 20:44:19 GMT https://community.qlik.com/t5/QlikView/Trouble-allocating-in-a-pivot-table/m-p/410461#M152751 danielact 2013-01-09T20:44:19Z Re: Trouble allocating in a pivot table https://community.qlik.com/t5/QlikView/Trouble-allocating-in-a-pivot-table/m-p/410462#M152752 <HTML><HEAD></HEAD><BODY><P>See if this is what you are looking for:</P><P></P><P><SPAN style="font-size: 10pt;">=(<SPAN style="color: #0000ff;">Sum</SPAN>(<SPAN style="color: #800000;">Loss</SPAN>)/<SPAN style="color: #0000ff;">Sum</SPAN>(<SPAN style="color: #0000ff;">total</SPAN>&lt;<SPAN style="color: #800000;">State</SPAN>&gt; <SPAN style="color: #0000ff;">aggr</SPAN>(<SPAN style="color: #0000ff;">distinct</SPAN> <SPAN style="color: #0000ff;">Sum</SPAN>(<SPAN style="color: #800000;">Loss</SPAN>), <SPAN style="color: #800000;">State</SPAN>, <SPAN style="color: #800000;">County</SPAN>)))*<SPAN style="color: #0000ff;">Sum</SPAN>(<SPAN style="color: #800000;">Premium</SPAN>) </SPAN></P></BODY></HTML> Wed, 09 Jan 2013 21:34:53 GMT https://community.qlik.com/t5/QlikView/Trouble-allocating-in-a-pivot-table/m-p/410462#M152752 2013-01-09T21:34:53Z