Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Account No | RM Name | Revenue | % Percentage of RM | Others... |
---|---|---|---|---|
001 | AA | 100 | column(2) / sum({<Month_Name = {'$(vCurMonth)'}, RM_Name = {column(1)}>} total Revenue) | |
002 | AA | 200 | ||
003 | BB | 300 | ||
004 | CC | 400 |
In straight table, we se Account No as dimension column. And want to calculate % Percentage of RM, like below
each account's Revenue / RM's toal revenue.
e.g. for account 001
% Percentage of RM = 100 / (100 + 200)
The question is : How can we assign value of RM Name into above set analysis?
Hi Jian,
I would do this on the script part, since you need the sum for each individual [RM Name].
Something like this:
data:
load * Inline
[
Account No,RM Name,Revenue
001, AA, 100
002, AA, 200
003, BB, 300
004, CC, 400
];
left join (data)
load
[RM Name],
sum(Revenue) as Denominator
Resident x
group by [RM Name];
The result would be that the sum of the Revenue by [RM Name] to be joined as a column on the data table, giving the bellow picture:
Felipe.