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:


      if(Dimensionality()=4,

       

      // 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.

       

      Greetings,

       

      Martijn Wanders