# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for
Did you mean:
Creator

## Sum by group

Hi - I have attached file.

 ID BU Allocation AAA 200 -28247 AAA 200 -27893.00 AAA 200 -28558.00 AAA 504 19699.30 AAA 504 19484.7793 AAA 504 19240.59 AAA 501 8652.41 AAA 501 8762.219727 AAA 501 8858.70 CCC 200 -3540.00 CCC 200 -3172 CCC 200 -2771.00 CCC 556 745.12 CCC 556 852.9500122 CCC 556 951.91 CCC 508 2025.88 CCC 508 2319.050049 CCC 508 2588.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 (4)

• ### swuel

1 Solution

Accepted Solutions
Partner

Great, use this script:

``````tmpTable:
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:
*
Resident tmpTable
Where BU <> 200
;

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

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

FinalTable:
*,
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:

Jordy

Climber

Work smarter, not harder
8 Replies
Partner

H Apthansh,

Use this formula:

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

Jordy

Climber

Work smarter, not harder
Creator
Author

Thank you,

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

Creator
Author

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

Creator
Author

@Sunny @sunny_talwar  any thoughts please ?

Partner

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

Jordy

Climber

Work smarter, not harder
Creator
Author
 ID BU Sum Allocation AAA 200 -84698 AAA 501 26273.33 31.01 AAA 504 58424.66 68.9 CCC 200 -9483 CCC 556 2549.97 26.99 CCC 508 6933.02 73.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 %

Partner

Great, use this script:

``````tmpTable:
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:
*
Resident tmpTable
Where BU <> 200
;

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

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

FinalTable:
*,
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:

Jordy

Climber

Work smarter, not harder
Creator
Author

Awesome ! Thank you much !

Tags