Summing children amount on parent data

[fabrice_lattern]

Hello everybody !

I am struggling to build an aggregate function. I have a kind of hierarchy in my data (without having define a hierarchy in the load script) :

DEAL_NUMBER, PARENT_DEAL_NUMBER, AMOUNT

Now, I am trying to build a pivot table with the DEAL_NUMBER as dimension, showing :

- The amount of the deal
- The amount of the children deals

I am trying to do something like this, but of course it does not work as my dimension in the pivot is the DEAL_NUMBER and not the PARENT_DEAL_NUMBER, and I do not know of to put this amount in front of the parent deal.

AGGR(SUM(AMOUNT), PARENT_DEAL_KEY)

Here is an example of what should be achieved

Data:

DEAL_NUMBER	PARENT_DEAL_NUMBER	AMOUNT
A		100
B	A	20
C	A	10

 
Result:

DEAL_NUMBER	AMOUNT	CHILDREN AMOUNT
A	100	30
B	20
C	10

Thanks in advance for any advice !

[sunny_talwar]

Is it possible to assign the same DEAL_NUMBER to PARENT_DEALER_NUMBER when PARENT_DEALER_NUMBER is null or blank? What I mean is to do this in the script?

DEAL_NUMBER	PARENT_DEAL_NUMBER	AMOUNT
A	A	100
B	A	20
C	A	10

Once you do this, it becomes easy and you can do like this

If(DEAL_NUMBER = PARENT_DEAL_NUMBER,
  Sum(TOTAL <PARENT_DEAL_NUMBER> 
    If(DEAL_NUMBER <> PARENT_DEAL_NUMBER, AMOUNT
    )
  )
)

[fabrice_lattern]

Hello,

First of all, thanks for your answer.

I do not think it is feasible. I am going to explain a bit more the context : we have some intercompany business (internal deals), then we sell our product to a end customer (external deals).

So sometimes we have internal deal (C) linked to internal deal (B) linked to external deal (A) (it is the max imbrication allowed, there is no four level hierarchy).

DEAL_NUMBER	PARENT_DEAL_NUMBER	TYPOLOGY	AMOUNT
A		EXTERNAL	1000
B	A	INTERNAL	100
C	B	INTERNAL	20

I still have the same requirement :  sum the amount of deal C in front of deal B (for some intercompany margin elimination purpose), but deal B has already a parent deal number (A), that I cannot change.

And if I use a technical field I guess it will be missleading for the report users.  

[sunny_talwar]

May be you need to set this up as a Hierarchy load in the script?