Extract existing column's value into set analysis.

[alex_stone]

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?

[felipedl]

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.