Calculated Field

[#1]

Hi All,

I have a table with fields Customer ID, Order Amount, Delivered Amount. How can I say Ordered Amount - Delivered Amount and only return in a straight chart amounts that are greater than zero i.e. only the customer ID's with outstanding orders. I also need the calculated field showing i.e telling me how much is outstanding.

Thank you

Herbiec09

[Anonymous]

may be some sample data with expected output will help give you a better response.

in general, your customer id would be in dimension of a chart and expressions could look like

=[Ordered Amount]-[Delivered Amount]

suppressing values that are zero can be done in a few ways - order by Y value in descending order or with an if condition, etc.....that is why a sample data and expected output will help.

[#3]

Apologies, the amount ordered and delivered fields are in 2 different tables linked by the customer ID

[Anonymous · Accepted solution]

Herbet,

If you want it in a straight table, it would have one dimension "Customer ID", and up to three expressions:
sum("Order Amount")
sum("Delivered Amount")
sum("Order Amount") - sum("Delivered Amount")

If you want to show only Customers where sum("Order Amount") - sum("Delivered Amount") > 0, replace dimension "Customer ID" with a calculated dimension

aggr(if(sum("Order Amount") - sum("Delivered Amount") > 0, "Customer ID"),"Customer ID")

Check the box "suppress when value is null".

Regards,
Michael

[#5]

Awesome, just what the doctor ordered. Thanks Michael

One last question, how can I incorporate other dimensions eg customer name, city etc.

Much appreciated

Thanks

Herbert

[Anonymous]

... how can I incorporate other dimensions eg customer name, city etc...

Sure - just add them as more dimensions.  I don't expect any issues, if they are the attributes of the customer.  Or you can even add them as expressions.

[#7]

Thank you Michael,

I appreciate your help.

Herbert