3 Replies Latest reply: Jul 8, 2018 3:09 PM by Annette Hartnoll RSS

    Distributing Values in a Script or Pivot Table

    Annette Hartnoll



      I'm fairly new to Qlik Sense and I'm trying to do something fairly complicated and would really appreciate some advice.  Currently we have a revenue report in Excel that we want to move to Qlik Sense.  The calculations in Excel are as follows:


      Raw Data:


      Step 1 is to distribute the segmented amount (-131) across the other three segments based on percentage of current amounts in each Segment:


      Step 2 is to distribute the revised "Unallocated Location" amounts across the other four locations based on percentage of current amounts in each Location:


      Final table will have no "Unsegmented" amounts and no "Unallocated" amounts.


      I don't know whether it's better to perform the calculations in the load script or via set analysis in a pivot table.  Any advise and steps would be appreciated.

        • Re: Distributing Values in a Script or Pivot Table
          Petter Skjolden

          I did a "load script" solution to your problem - I don't know if a UI-solution directly would be easier or not. Maybe I give it a go later for the fun of it...


          2018-07-06 09_47_22-.png


          2018-07-06 09_47_42-Qlik Sense Desktop.png


          I have attached the solution as a Qlik Sense App.


          I haven't explained much - but please feel free to ask any question as to what the different parts in the load script does....

          • Re: Distributing Values in a Script or Pivot Table
            kaan erisen

            Hi Annette,





                "Segment 1"+(("Segment 1"/RangeSum("Segment 1", "Segment 2", "Segment 3"))*Unsegmented) as "Segment 1",

                "Segment 2"+(("Segment 2"/RangeSum("Segment 1", "Segment 2", "Segment 3"))*Unsegmented) as "Segment 2",

                "Segment 3"+(("Segment 3"/RangeSum("Segment 1", "Segment 2", "Segment 3"))*Unsegmented) as "Segment 3"

              INLINE [

              Location, "Segment 1", "Segment 2", "Segment 3", Unsegmented

              Location #1, 0, 958649, 43011,0

              Location #2, 0, 1806262, 65869,0

              Location #3, 0, 1613985, 36336,0

              Location #4, 0, 333660, 15395,0

              Unallocated, 0, -184, -142, -131





              sum("Segment 1") as "Segment 1 Non Tot",

            sum("Segment 2") as "Segment 2 Non Tot",

            sum("Segment 3") as "Segment 3 Non Tot"

              Resident Source

              where Location<>'Unallocated';




              sum("Segment 1") as "Segment 1 Un Tot",

            sum("Segment 2") as "Segment 2 Un Tot",

            sum("Segment 3") as "Segment 3 Un Tot"

              Resident Source

              where Location='Unallocated';







                alt("Segment 1"+("Segment 1"/FieldValue('Segment 1 Non Tot',1))*FieldValue('Segment 1 Un Tot',1),0) as "Segment 1",

                alt("Segment 2"+("Segment 2"/FieldValue('Segment 2 Non Tot',1))*FieldValue('Segment 2 Un Tot',1),0) as "Segment 2",

                alt("Segment 3"+("Segment 3"/FieldValue('Segment 3 Non Tot',1))*FieldValue('Segment 3 Un Tot',1),0) as "Segment 3"

               Resident Source

              where Location<>'Unallocated'; 


              drop tables Source,Non_Unlocated_temp,Unlocated_temp;




            If you want to unpivot that data, you need to switch comment line between noconcatenate and Crosstable.






            Hope it helps,