Problem with sum() function and total

[#1]

Hi,

I have a straight table with one dimension and one expression. Dimension is Company. Expression is shown here:

sum( (Payment *-1 / sum(TOTAL <Company>  Payment))*T)

With the example data, the straight table shows 2 rows, one for Company A and one for Company B.

I have problems with this part: sum(TOTAL <Company>  Payment). It calculates as a standalone expression, but as part of the expression above, it does not.

My expression calculates if I remove <Company>, but obviously then it does not calculate what I need. I want it to sum Payments over the dimension Company, so when calculating Company A, it sums up Payment only for company A, likewise with Company B.

Any help is greatly appreciated.

-Bjorn

Data:                                                                                        

Company	Payment	T
A	-22500	-0,003
A	-22500	-0,499
A	-22500	-1,003
A	-22500	-1,501
A	-22500	-2,005
A	-22500	-2,501
A	-22500	-3,005
A	-22500	-3,501
B	-1287142	-0,545
B	-1287142	-1,548
B	-1287142	-2,548
B	-1287142	-3,548
B	-1287142	-4,548
B	-1287142	-5,551
B	-1287142	-6,551

[Gysbert_Wassenaar · Accepted solution]

You're nesting aggregation functions, but haven't applied the necessary aggr function. Try this expression:

sum( (Payment *-1 / aggr(sum(TOTAL <Company>  Payment),Company,Payment,T)*T)

[#3]

Hi,

may be use by if and len functions. like this.It will work.

If(Len(company>0, .........................)

[#4]

Thanks Gysbert, it works.

-Bjorn