Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Distributing Values in a Script or Pivot Table

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.

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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

View solution in original post

3 Replies
petter
Partner - Champion III
Partner - Champion III

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

kaanerisen
Creator III
Creator III

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,

Anonymous
Not applicable
Author

Thank you Petter.  This is perfect.