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

    Distributing Values in a Script or Pivot Table

    Annette Hartnoll

      Hi,

       

      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:

      Excel1.png

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

      Excel2.png

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

      Excel3.png

      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,

             

              Source:

              LOAD

                Location,

                "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

              ];

             

              Non_Unlocated_temp:

              load

              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';

             

              Unlocated_temp:

               load

              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';

             

              Master:

              NoConcatenate

              //CrossTable(Segment,Revenue)

              Load

                Location,

                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;

             

            Untitled.png

             

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

            Untitled3.png

            Untitled4.png

             

            Untitled2.png

             

            Hope it helps,