product mix

Report Inappropriate Content · ‎2010-01-21

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

johnw · ‎2010-01-21

Here's one approach. See attached.

pick(dimensionality()
,1
,sum(QTY)/sum(total <MONTH> QTY)
,sum(QTY)/sum(total <MONTH,REGION> QTY))

Related Topics