Distributing Values in a Script or Pivot Table

[Anonymous]

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:

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.

[petter · Accepted solution]

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

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

[kaanerisen]

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;

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

Hope it helps,

[Anonymous]

Thank you Petter.  This is perfect.