Sub Total over Calculate Dimension in Pivot Table - Qlik Community

Report Inappropriate Content · ‎2010-09-21

<body>Hi everyone, I have a problem calculating a SubTotal for a Calculated Dimension in a Pivot Table. Let's see a table sample: <col width="108"></col> <col span="10" width="111"></col> <table> <tbody> <tr height="20"> <td width="108" height="20">Segment</td> <td width="111">Cluster 1</td> <td width="111">Cluster 1 Total</td> <td width="111">Cluster 2</td> <td width="111">Cluster 2 Total</td> <td width="111">Cluster 3</td> <td width="111">Cluster 3 Total</td> <td width="111">Cluster 4</td> <td width="111">Cluster 4 Total</td> <td width="111">Cluster 5</td> <td width="111">Cluster 5 Total</td> </tr> <tr height="20"> <td height="20">A</td> <td align="right">31779</td> <td align="right">885504</td> <td align="right">12375</td> <td align="right">885504</td> <td align="right">4129</td> <td align="right">885504</td> <td align="right">2987</td> <td align="right">885504</td> <td align="right">151</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">B</td> <td align="right">49740</td> <td align="right">885504</td> <td align="right">18018</td> <td align="right">885504</td> <td align="right">5570</td> <td align="right">885504</td> <td align="right">3913</td> <td align="right">885504</td> <td align="right">177</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">C</td> <td align="right">16604</td> <td align="right">885504</td> <td align="right">5703</td> <td align="right">885504</td> <td align="right">2065</td> <td align="right">885504</td> <td align="right">1270</td> <td align="right">885504</td> <td align="right">45</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">D</td> <td align="right">106287</td> <td align="right">885504</td> <td align="right">33177</td> <td align="right">885504</td> <td align="right">11640</td> <td align="right">885504</td> <td align="right">7698</td> <td align="right">885504</td> <td align="right">273</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">E</td> <td align="right">74546</td> <td align="right">885504</td> <td align="right">18409</td> <td align="right">885504</td> <td align="right">7094</td> <td align="right">885504</td> <td align="right">5044</td> <td align="right">885504</td> <td align="right">233</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">F</td> <td align="right">171076</td> <td align="right">885504</td> <td align="right">42561</td> <td align="right">885504</td> <td align="right">16930</td> <td align="right">885504</td> <td align="right">11843</td> <td align="right">885504</td> <td align="right">329</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">G</td> <td align="right">11525</td> <td align="right">885504</td> <td align="right">4228</td> <td align="right">885504</td> <td align="right">1125</td> <td align="right">885504</td> <td align="right">1221</td> <td align="right">885504</td> <td align="right">35</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">H</td> <td align="right">1304</td> <td align="right">885504</td> <td align="right">543</td> <td align="right">885504</td> <td align="right">115</td> <td align="right">885504</td> <td align="right">128</td> <td align="right">885504</td> <td align="right">4</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">I</td> <td align="right">9037</td> <td align="right">885504</td> <td align="right">2541</td> <td align="right">885504</td> <td align="right">915</td> <td align="right">885504</td> <td align="right">929</td> <td align="right">885504</td> <td align="right">17</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">J</td> <td align="right">16802</td> <td align="right">885504</td> <td align="right">3669</td> <td align="right">885504</td> <td align="right">1514</td> <td align="right">885504</td> <td align="right">1371</td> <td align="right">885504</td> <td align="right">23</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">K</td> <td align="right">11139</td> <td align="right">885504</td> <td align="right">2294</td> <td align="right">885504</td> <td align="right">1154</td> <td align="right">885504</td> <td align="right">967</td> <td align="right">885504</td> <td align="right">18</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">Z</td> <td align="right">7225</td> <td align="right">885504</td> <td align="right">2197</td> <td align="right">885504</td> <td align="right">828</td> <td align="right">885504</td> <td align="right">598</td> <td align="right">885504</td> <td align="right">28</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">-</td> <td align="right">88032</td> <td align="right">885504</td> <td align="right">31922</td> <td align="right">885504</td> <td align="right">7694</td> <td align="right">885504</td> <td align="right">12187</td> <td align="right">885504</td> <td align="right">509</td> <td align="right">885504</td> </tr> <tr height="20"> <td height="20">Segment Total</td> <td align="right">595096</td> <td></td> <td align="right">177637</td> <td></td> <td align="right">60773</td> <td></td> <td align="right">50156</td> <td></td> <td align="right">1842</td> <td></td> </tr> </tbody> </table> For each [Cluster x Total] I need the total of the [Cluster X] over all segments. Usually this problem is avoided with the use of total<[FIELD]> command. This time is more complex because I don't have a [FIELD], but I have a calculated dimension. In this dimension I use a series of if to define the Cluster X of each Client. For easy access I created a variable of the dimension. Does anyone knows how to solve this problem? Thanks for your help. Marco</body>

Oleg_Troyansky · ‎2010-09-21

Marco,

I'd recommend taking the "series of IF to define cluster" calculation back into the load script (if possible) and avoid a heavy calculation in the runtime. Then you'll also have a Field instead of the calculated Dimension, and that will ultimately solve your problem.

cheers,

To become a Cloud Master, attend Masters Summit for Qlik in Hamburg
To learn Expert development techniques, join my Qlik Expert Class in Vienna

Report Inappropriate Content · ‎2010-09-21

I can't because the Cluster calculation depends on the filters I choose. For example the year or the store.