Hi,
I am familiar with how to add calculated fields to pivot tables but so far I have only been successful when I want to calculate something using the actual values in the fields.
In this one spreadsheet,
There are codes for items (ie. each code is a 9 digit number assigned to a particular item in stock), and counts for those items that were sold. These are listed separately for each month, as in:
<table style="border-collapse: collapse; width: 507pt;" width="676" border="0" cellpadding="0" cellspacing="0"><col style="width: 125pt;" width="166"> <col style="width: 123pt;" width="164"> <col style="width: 68pt;" width="91"> <col style="width: 123pt;" width="164"> <col style="width: 68pt;" width="91"> <tbody><tr style="height: 15pt;" height="20"> <td style="background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; width: 125pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" width="166" height="20">
</td> <td class="xl63" style="background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 123pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" width="164" align="right">9/1/2009</td> <td style="background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 68pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" width="91">
</td> <td class="xl63" style="background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 123pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" width="164" align="right">10/1/2009</td> <td style="background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 68pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" width="91">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="20">Row Labels</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> Count of codes</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> sum of items sold
</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> Count of codes</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Sum of items sold
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dealer 1
</td> <td align="right">4799</td> <td align="right">46</td> <td align="right">3992</td> <td align="right">39</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dealer 2
</td> <td align="right">1288</td> <td align="right">2</td> <td align="right">3134</td> <td align="right">23</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dealer 3
</td> <td align="right">1952</td> <td align="right">39</td> <td align="right">1258</td> <td align="right">22</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dealer 4
</td> <td align="right">1289</td> <td align="right">25</td> <td align="right">1013</td> <td align="right">17</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dealer 5
</td> <td align="right">1825</td> <td align="right">4</td> <td align="right">2058</td> <td align="right">11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dealer 6
</td> <td align="right">245</td> <td align="right">3</td> <td align="right">193</td> <td align="right">0</td> </tr> </tbody></table>
etc.
I want to calculate the conversion for each dealer, for each month.
In other words:
(sum of items sold)/(count of codes)
for every single month and at the end, for the year.
This would mean adding a third column within every month.
I can do this by doing GETPIVOTDATA for each row, but I'd rather have something integrated to the pivot table, so it would be automatic every month.
I tried adding a calculated field, but that would only let me do
ITEMS/CODES
Items is fine but for CODES, it ADDS the codes (9 digit numbers), instead of using the count of codes in the table.
Can someone please help? Thanks !!
nazatron
I am familiar with how to add calculated fields to pivot tables but so far I have only been successful when I want to calculate something using the actual values in the fields.
In this one spreadsheet,
There are codes for items (ie. each code is a 9 digit number assigned to a particular item in stock), and counts for those items that were sold. These are listed separately for each month, as in:
<table style="border-collapse: collapse; width: 507pt;" width="676" border="0" cellpadding="0" cellspacing="0"><col style="width: 125pt;" width="166"> <col style="width: 123pt;" width="164"> <col style="width: 68pt;" width="91"> <col style="width: 123pt;" width="164"> <col style="width: 68pt;" width="91"> <tbody><tr style="height: 15pt;" height="20"> <td style="background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; width: 125pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" width="166" height="20">
</td> <td class="xl63" style="background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 123pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" width="164" align="right">9/1/2009</td> <td style="background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 68pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" width="91">
</td> <td class="xl63" style="background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 123pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" width="164" align="right">10/1/2009</td> <td style="background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 68pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" width="91">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="20">Row Labels</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> Count of codes</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> sum of items sold
</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;"> Count of codes</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color rgb(149, 179, 215); border-width: medium medium 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Sum of items sold
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dealer 1
</td> <td align="right">4799</td> <td align="right">46</td> <td align="right">3992</td> <td align="right">39</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dealer 2
</td> <td align="right">1288</td> <td align="right">2</td> <td align="right">3134</td> <td align="right">23</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dealer 3
</td> <td align="right">1952</td> <td align="right">39</td> <td align="right">1258</td> <td align="right">22</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dealer 4
</td> <td align="right">1289</td> <td align="right">25</td> <td align="right">1013</td> <td align="right">17</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dealer 5
</td> <td align="right">1825</td> <td align="right">4</td> <td align="right">2058</td> <td align="right">11</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">dealer 6
</td> <td align="right">245</td> <td align="right">3</td> <td align="right">193</td> <td align="right">0</td> </tr> </tbody></table>
etc.
I want to calculate the conversion for each dealer, for each month.
In other words:
(sum of items sold)/(count of codes)
for every single month and at the end, for the year.
This would mean adding a third column within every month.
I can do this by doing GETPIVOTDATA for each row, but I'd rather have something integrated to the pivot table, so it would be automatic every month.
I tried adding a calculated field, but that would only let me do
ITEMS/CODES
Items is fine but for CODES, it ADDS the codes (9 digit numbers), instead of using the count of codes in the table.
Can someone please help? Thanks !!
nazatron
Last edited:
