1 Reply Latest reply: Jan 21, 2010 7:23 PM by John Witherspoon RSS

    product mix

      <body><p>ciao</p> <p>i have a pivot table like the following</p> <p> </p> <table width="340" cellpadding="0" cellspacing="0" border="0"> <tbody> <tr> <td valign="bottom" width="104"> <p>DOCUMENT DATE</p> </td> <td valign="bottom" width="63"> <p>PRODUCT</p> </td> <td valign="bottom" width="28"> <p>QTY</p> </td> <td x:str="'+month(document date)" valign="bottom" width="145"> <p>+month(document date)</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">01/01/2010</p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="28"> <p align="right">10</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">01/01/2010</p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="28"> <p align="right">5</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">01/01/2010</p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="28"> <p align="right">8</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">01/01/2010</p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="28"> <p align="right">14</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">02/01/2010</p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="28"> <p align="right">10</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">02/01/2010</p> </td> <td valign="bottom" width="63"> <p>PLUTO</p> </td> <td valign="bottom" width="28"> <p align="right">4</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">02/02/2010</p> </td> <td valign="bottom" width="63"> <p>PAPERINO</p> </td> <td valign="bottom" width="28"> <p align="right">77</p> </td> <td valign="bottom" width="145"> <p>feb</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">03/02/2010</p> </td> <td valign="bottom" width="63"> <p>MINNI</p> </td> <td valign="bottom" width="28"> <p align="right">55</p> </td> <td valign="bottom" width="145"> <p>feb</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">04/02/2010</p> </td> <td valign="bottom" width="63"> <p>PLUTO</p> </td> <td valign="bottom" width="28"> <p align="right">41</p> </td> <td valign="bottom" width="145"> <p>feb</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">02/01/2010</p> </td> <td valign="bottom" width="63"> <p>PAPERINO</p> </td> <td valign="bottom" width="28"> <p align="right">23</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">02/01/2010</p> </td> <td valign="bottom" width="63"> <p>MINNI</p> </td> <td valign="bottom" width="28"> <p align="right">11</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">02/01/2010</p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="28"> <p align="right">22</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">03/01/2010</p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="28"> <p align="right">14</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">01/01/2010</p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="28"> <p align="right">10</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">01/01/2010</p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="28"> <p align="right">12</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">03/01/2010</p> </td> <td valign="bottom" width="63"> <p>PAPERINO</p> </td> <td valign="bottom" width="28"> <p align="right">10</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">03/01/2010</p> </td> <td valign="bottom" width="63"> <p>PAPERINO</p> </td> <td valign="bottom" width="28"> <p align="right">13</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">03/01/2010</p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="28"> <p align="right">9</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> <tr> <td valign="bottom" width="104"> <p align="right">01/01/2010</p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="28"> <p align="right">10</p> </td> <td valign="bottom" width="145"> <p>jan</p> </td> </tr> </tbody> </table> <p> </p> <p>and i want to get to the following:</p> <p> </p> <p> </p> <table width="311" cellpadding="0" cellspacing="0" border="0"> <tbody> <tr> <td valign="bottom" width="115"> <p>Somma di QTY</p> </td> <td valign="bottom" width="63"> <p> </p> </td> <td valign="bottom" colspan="4" width="133"> <p align="center">+month(document date)</p> </td> </tr> <tr> <td valign="bottom" width="115"> <p>REGION</p> </td> <td valign="bottom" width="63"> <p>PRODUCT</p> </td> <td valign="bottom" width="30"> <p align="center">jan</p> </td> <td valign="bottom" width="32"> <p align="center">mix</p> </td> <td valign="bottom" width="30"> <p align="center">feb</p> </td> <td valign="bottom" width="41"> <p align="center">mix</p> </td> </tr> <tr> <td valign="bottom" width="115"> <p>MILANO</p> </td> <td valign="bottom" width="63"> <p>PAPERINO</p> </td> <td valign="bottom" width="30"> <p align="center">10</p> </td> <td x:fmla="=+C3/C5" x:num="0.55555555555555558" valign="bottom" width="32"> <p align="center">56%</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="41"> <p align="center"> </p> </td> </tr> <tr> <td valign="bottom" width="115"> <p> </p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="30"> <p align="center">8</p> </td> <td x:fmla="=+C4/C5" x:num="0.44444444444444442" valign="bottom" width="32"> <p align="center">44%</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="41"> <p align="center"> </p> </td> </tr> <tr> <td valign="bottom" width="115"> <p>MILANO Totale</p> </td> <td valign="bottom" width="63"> <p> </p> </td> <td valign="bottom" width="30"> <p align="center">18</p> </td> <td x:fmla="=+C5/C19" x:num="9.7297297297297303E-2" valign="bottom" width="32"> <p align="center">10%</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="41"> <p align="center"> </p> </td> </tr> <tr> <td valign="bottom" width="115"> <p>PESCARA</p> </td> <td valign="bottom" width="63"> <p>MINNI</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="32"> <p align="center"> </p> </td> <td valign="bottom" width="30"> <p align="center">55</p> </td> <td x:fmla="=+IF(E6=0,0,E6/E10)" x:num="0.41666666666666669" valign="bottom" width="41"> <p align="center">42%</p> </td> </tr> <tr> <td valign="bottom" width="115"> <p> </p> </td> <td valign="bottom" width="63"> <p>PAPERINO</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="32"> <p align="center"> </p> </td> <td valign="bottom" width="30"> <p align="center">77</p> </td> <td x:fmla="=+E7/E10" x:num="0.58333333333333337" valign="bottom" width="41"> <p align="center">58%</p> </td> </tr> <tr> <td valign="bottom" width="115"> <p> </p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="30"> <p align="center">75</p> </td> <td x:fmla="=+IF(C8=0,0,C8/$C$10)" x:num="0.94936708860759489" valign="bottom" width="32"> <p align="center">95%</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="41"> <p align="center"> </p> </td> </tr> <tr> <td valign="bottom" width="115"> <p> </p> </td> <td valign="bottom" width="63"> <p>PLUTO</p> </td> <td valign="bottom" width="30"> <p align="center">4</p> </td> <td x:fmla="=+IF(C9=0,0,C9/$C$10)" x:num="5.0632911392405063E-2" valign="bottom" width="32"> <p align="center">5%</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="41"> <p align="center"> </p> </td> </tr> <tr> <td valign="bottom" width="115"> <p>PESCARA Totale</p> </td> <td valign="bottom" width="63"> <p> </p> </td> <td valign="bottom" width="30"> <p align="center">79</p> </td> <td x:fmla="=+C10/C19" x:num="0.42702702702702705" valign="bottom" width="32"> <p align="center">43%</p> </td> <td valign="bottom" width="30"> <p align="center">132</p> </td> <td x:fmla="=+E10/E19" x:num="0.76300578034682076" valign="bottom" width="41"> <p align="center">76%</p> </td> </tr> <tr> <td valign="bottom" width="115"> <p>ROMA</p> </td> <td valign="bottom" width="63"> <p>MINNI</p> </td> <td valign="bottom" width="30"> <p align="center">11</p> </td> <td x:fmla="=+C11/C15" x:num="0.18032786885245902" valign="bottom" width="32"> <p align="center">18%</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="41"> <p align="center"> </p> </td> </tr> <tr> <td valign="bottom" width="115"> <p> </p> </td> <td valign="bottom" width="63"> <p>PAPERINO</p> </td> <td valign="bottom" width="30"> <p align="center">23</p> </td> <td x:fmla="=+C12/C15" x:num="0.37704918032786883" valign="bottom" width="32"> <p align="center">38%</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="41"> <p align="center"> </p> </td> </tr> <tr> <td valign="bottom" width="115"> <p> </p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="30"> <p align="center">27</p> </td> <td x:fmla="=+C13/C15" x:num="0.44262295081967212" valign="bottom" width="32"> <p align="center">44%</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="41"> <p align="center"> </p> </td> </tr> <tr> <td valign="bottom" width="115"> <p> </p> </td> <td valign="bottom" width="63"> <p>PLUTO</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="32"> <p align="center"> </p> </td> <td valign="bottom" width="30"> <p align="center">41</p> </td> <td x:fmla="=+E14/E15" x:num="1" valign="bottom" width="41"> <p align="center">100%</p> </td> </tr> <tr> <td valign="bottom" width="115"> <p>ROMA Totale</p> </td> <td valign="bottom" width="63"> <p> </p> </td> <td valign="bottom" width="30"> <p align="center">61</p> </td> <td x:fmla="=+C15/C19" x:num="0.32972972972972975" valign="bottom" width="32"> <p align="center">33%</p> </td> <td valign="bottom" width="30"> <p align="center">41</p> </td> <td x:fmla="=+E15/E19" x:num="0.23699421965317918" valign="bottom" width="41"> <p align="center">24%</p> </td> </tr> <tr> <td valign="bottom" width="115"> <p>TORINO</p> </td> <td valign="bottom" width="63"> <p>PAPERINO</p> </td> <td valign="bottom" width="30"> <p align="center">13</p> </td> <td x:fmla="=+C16/C17" x:num="0.9285714285714286" valign="bottom" width="32"> <p align="center">93%</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="41"> <p align="center"> </p> </td> </tr> <tr> <td valign="bottom" width="115"> <p> </p> </td> <td valign="bottom" width="63"> <p>PIPPO</p> </td> <td valign="bottom" width="30"> <p align="center">14</p> </td> <td x:fmla="=+C17/C18" x:num="0.51851851851851849" valign="bottom" width="32"> <p align="center">52%</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="41"> <p align="center"> </p> </td> </tr> <tr> <td valign="bottom" width="115"> <p>TORINO Totale</p> </td> <td valign="bottom" width="63"> <p> </p> </td> <td valign="bottom" width="30"> <p align="center">27</p> </td> <td x:fmla="=+C18/C19" x:num="0.14594594594594595" valign="bottom" width="32"> <p align="center">15%</p> </td> <td valign="bottom" width="30"> <p align="center"> </p> </td> <td valign="bottom" width="41"> <p align="center"> </p> </td> </tr> <tr> <td valign="bottom" width="115"> <p>Totale complessivo</p> </td> <td valign="bottom" width="63"> <p> </p> </td> <td valign="bottom" width="30"> <p align="center">185</p> </td> <td valign="bottom" width="32"> <p align="center"> </p> </td> <td valign="bottom" width="30"> <p align="center">173</p> </td> <td valign="bottom" width="41"> <p align="center"> </p> </td> </tr> </tbody> </table> <p> </p> <p>how can i get automatically to the "mix" formula in qlik?</p> <p>thanks for yout help</p> <p>francesca</p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p><col width="104"></col><col width="63"></col><col width="28"></col><col width="145"></col></p> <p> </p> <p> </p> <p> </p> <p> </p></body>