1 Reply Latest reply: Sep 27, 2018 9:53 AM by Martijn Wanders RSS

    Help with calculated row in pivot table QlikSense

    Martijn Wanders

      Hi all,


      A couple of days i'm struggling to add a calculated row in my pivot table. I read a lot on the Qlik forum but don't manage to implement something working. In the attachment you can find a screenshot. Below "Inkopen" and above "Omzet/reclame fee"I want a calculated row (where the red line is).


      This is what I want to calculate:

      (Omzet - Inkopen)/Omzet = Margin


      So below "Inkopen" I want a new row with Margin and the result of the calculation above.


      I read about dimensionality function. I found this formula which add a row with the result:


      if(Dimensionality()=1, count(Sales), num(count({<Region={'Scotland'}>}Sales)/count({<Region={'England'}>}Sales),'#0%'))

      If I want to use this for my challenge then:



      // The following is the used expression in first measure. It's count the revenue in current year (Boekjaar), specific accounts (grootboekrekening) and period.

           num(-sum({<Boekjaar={$(=max(Boekjaar))},[Grootboekrekening Soort]={'R'},Periode={"<=$(=MAX(Periode))"}>}[Bedrag Saldo]),'#.##0'),


      // The second part:

           num((sum({<Mapping_N2={'Omzet'}>}Sales) - Sum({<Mapping_2={'Inkopen'} / sum({<Mapping_N2={'Omzet'}>}Sales) >})Sales),'#0%'))


      Where "Sales" is red I don't know what to do? This is calculated with the same expression mention above. At the red mentioned sales there must be another set analysis I think.


      Can somebody help me? Thank you in advance and would really appreciate it. More clarification? No problem and give a shout.




      Martijn Wanders