Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ahartnoll
New Contributor

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

Re: Distributing Values in a Script or Pivot Table

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

3 Replies
MVP
MVP

Re: Distributing Values in a Script or Pivot Table

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
Contributor III

Re: Distributing Values in a Script or Pivot Table

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,

ahartnoll
New Contributor

Re: Distributing Values in a Script or Pivot Table

Thank you Petter.  This is perfect.

Community Browser