Sum by group

[apthansh]

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%

[JordyWegman]

H Apthansh,

Use this formula:

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

Jordy

Climber

[apthansh]

Thank you,

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

[apthansh]

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

[apthansh]

@Sunny @sunny_talwar  any thoughts please ?

[JordyWegman]

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

Jordy

Climber

[apthansh]

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 %
                             

[JordyWegman · Accepted solution]

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:

Jordy

Climber

[apthansh]

Awesome ! Thank you much !