Correct display positive and negative amount an calculation

[PGesterkamp]

Hi There

I have a problem solving the issue in the Table below.

One collum (DC) indicates wether the amount has to  added or deducted from the total amount.  So when DC (Deb/Cred) is 'C' ,  the RentalDay  and the TotalAmount has to be Negative (and deducted in the summary amount)

I tried  like : IF(DC='D',  RentaDays, -RentalDays)  .... that wil display coorectly but stil  in the summary it wil add instead of deduct the number.  (same for the amounts).  What should be the correct set?

				(should be)
DC	Price	RentalDays	Total Amount	RentalDays	TotalAmount
D	€      1,00	6	€             6,00	6	€             6,00
C	€      2,00	2	€             4,00	-2	€           -4,00
D	€      2,00	9	€           18,00	2	€           18,00
D	€      3,00	5	€           15,00	3	€           15,00
C	€      1,00	1	€             1,00	-1	€           -1,00
SUM		23	€           44,00	8	€           34,00

[lironbaram · Accepted solution]

hi 

your issue is that the if function is evaluated in the total row once again so it gives you the result of what is in the total in column DC 

you have two options :

1.select in the expression total the method to be : "some of rows "

2. change your expression to sum(aggr( IF(DC='D',  RentaDays, -RentalDays) ,XXXX))

instead of the the XXXX in the expression you need to write your table dimensions 

[PGesterkamp]

Hi Again

Thanks verry much for the support (on a Sunday). Item 2 was the solution I was looking for