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: 
apthansh
Creator
Creator

Sum by group

Hi - I have attached file.

IDBUAllocation
AAA200-28247
AAA200-27893.00
AAA200-28558.00
AAA50419699.30
AAA50419484.7793
AAA50419240.59
AAA5018652.41
AAA5018762.219727
AAA5018858.70
   
CCC200-3540.00
CCC200-3172
CCC200-2771.00
CCC556745.12
CCC556852.9500122
CCC556951.91
CCC5082025.88
CCC5082319.050049
CCC5082588.09

 

I want to do Sum of Allocation by each BU and divide by Business Unit 200 for each ID.
BU 200 is common for each ID but other BU's varies throughout..I have almost 200 unique ID's..

Ex: ID = AAA.

Sum of Allocation for BU 200 = 84,698
Sum of Allocation for BU 504 = 58,424
Sum of Allocation for BU 501 = 26,273

Finally = 58,424/84,698 = 68.99 %
                                      +
                  26,273/84,698 = 31.01 %
                               = 100%

Ex: ID = CCC.

Sum of Allocation for BU 200 = 9,483
Sum of Allocation for BU 556 = 2,549
Sum of Allocation for BU 508 = 6,933

Finally = 2,549/9,483 = 26.99 %
                                      +
                  6,933/9,483 = 73.01 %
                               = 100%

 

Labels (3)
1 Solution

Accepted Solutions
JordyWegman
Partner - Master
Partner - Master

Great, use this script:

tmpTable:
Load * Inline [
ID,	BU,	Sum
AAA,	200,	84698 
AAA,	501,	26273.33
AAA,	504,	58424.66
CCC,	200,	9483
CCC,	556,	2549.97
CCC,	508,	6933.02
];

NoConcatenate
tmp2Table:
Load
*
Resident tmpTable
Where BU <> 200
;

Left Join(tmp2Table)
Load
 ID,
 Sum as SumTotal
Resident tmpTable
Where BU = 200
;

Concatenate(tmp2Table)
Load
*
Resident tmpTable
Where BU = 200
;

FinalTable:
Load
 *,
 Num(IF(not Isnull(SumTotal), Sum/SumTotal,null()),'#.#%') as Percentage
Resident tmp2Table
Order by ID asc, BU asc
;

Drop table tmpTable;
Drop table tmp2Table;

Output is this:

2019-07-11 17_09_51-Qlik Sense Desktop.png

Jordy

Climber

Work smarter, not harder

View solution in original post

8 Replies
JordyWegman
Partner - Master
Partner - Master

H Apthansh,

Use this formula:

Table:
Load
  ID 
  BU,
  Sum(Allocation) as Allocation)
From [YourSource] (qvd)
Group by ID, BU
;

Jordy

Climber

Work smarter, not harder
apthansh
Creator
Creator
Author

Thank you,

But how can I put Allocation by each BU in a table ?

apthansh
Creator
Creator
Author

I have attached the QVW and the excel file.Thank you.

apthansh
Creator
Creator
Author

@Sunny @sunny_talwar  any thoughts please ?

JordyWegman
Partner - Master
Partner - Master

What do you mean exactly? Because in the QVW you can make the image in the allocation.xlsx.

Jordy

Climber

Work smarter, not harder
apthansh
Creator
Creator
Author

IDBUSumAllocation
AAA200-84698 
AAA50126273.3331.01
AAA50458424.6668.9
CCC200-9483 
CCC5562549.9726.99
CCC5086933.0273.01

 

I want the above Allocation column in straight table based on the query below.


 

Ex: ID = AAA.

Sum of Allocation for BU 200 = 84,698
Sum of Allocation for BU 504 = 58,424
Sum of Allocation for BU 501 = 26,273

Finally = BU 504  =  58,424/84,698 = 68.99 %
                   BU 501 =  26,273/84,698 = 31.01 %

Ex: ID = CCC.

Sum of Allocation for BU 200 = 9,483
Sum of Allocation for BU 556 = 2,549
Sum of Allocation for BU 508 = 6,933

Finally = BU 556 = 2,549/9,483 = 26.99 %
                  BU 508 = 6,933/9,483 = 73.01 %
                             

JordyWegman
Partner - Master
Partner - Master

Great, use this script:

tmpTable:
Load * Inline [
ID,	BU,	Sum
AAA,	200,	84698 
AAA,	501,	26273.33
AAA,	504,	58424.66
CCC,	200,	9483
CCC,	556,	2549.97
CCC,	508,	6933.02
];

NoConcatenate
tmp2Table:
Load
*
Resident tmpTable
Where BU <> 200
;

Left Join(tmp2Table)
Load
 ID,
 Sum as SumTotal
Resident tmpTable
Where BU = 200
;

Concatenate(tmp2Table)
Load
*
Resident tmpTable
Where BU = 200
;

FinalTable:
Load
 *,
 Num(IF(not Isnull(SumTotal), Sum/SumTotal,null()),'#.#%') as Percentage
Resident tmp2Table
Order by ID asc, BU asc
;

Drop table tmpTable;
Drop table tmp2Table;

Output is this:

2019-07-11 17_09_51-Qlik Sense Desktop.png

Jordy

Climber

Work smarter, not harder
apthansh
Creator
Creator
Author

Awesome ! Thank you much !