How to define an expression for calculate the residual import in a table

[#1]

Good morning,

it's possibile to define an expression that allow me to obtain a field in the column called RESIDUAL (in the screen-shot there's the formula indicated for calculate) ?

Which function it's possible to use ?

Thanks a lot.

Giovanni

[Anonymous]

Try this and let me know...=Sum({<CAUSE={FE}>}PARTIAL AMOUNT)-Sum({<CAUSE={IP,IN,NC}>}PARTIAL AMOUNT)

[morganaaron]

Sum(Total <ID, [ID AMOUNT]> {<CAUSE={'FE'}>} [PARTIAL AMOUNT]) - Sum(Total <ID, [ID AMOUNT]> {<CAUSE={'IN', 'IP', 'NC'}>} [PARTIAL AMOUNT])

This will repeat the value for every Cause however. If you only want it to appear for one Cause (if FE always appears, for example) you could precede it with an if statement to limit where Cause = 'FE'.

[#4]

I wanted to specify that the value of the ID AMOUNT column is correct when the value of CASE is FE. In the other case the value is 0.

After the expression that they have send to me the result il the same and don't work correctly.

[morganaaron]

Ah so ID AMOUNT was an expression - you can omit this from the expression above then. You should only need to exclude the ID from the Total then, so that this becomes the only value to calculate over.

[#6]

The value of ID Amount is not an expression but is enhanced only when the field has the value CAUSES FE. In other cases, the value is equal to 0.

[morganaaron · Accepted solution]

So it's a calculated dimension? Why do you need it? Could you not use an expression to do the same? I.e. use Sum({<CAUSE={'FE'}>} [PARTIAL AMOUNT]) which would return a value where it's FE or 0 where it's not?

[#8]

Aaron, now the situation is this.

The ID Amount is the expressione that you tell me to use.

The residual now is correct. Thanks a lot.

It's possbile to have only 1 line for the ID, the line where the CAUSE is FE (and the value of residual is correct) ?

[morganaaron]

You could limit the Cause to only show where it's FE, but this would affect the residual calculation.

I'll have a think and see - there may be someone else able to help, maybe start another question on the forums for that specific issue and see if anyone else has ideas!

[#10]

Thanks Aaron for all the suggest that you give me.

Have a good day !!!

Thanks a lot !

Giovanni