Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
Apologies, the amount ordered and delivered fields are in 2 different tables linked by the customer ID
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
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
... 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.
Thank you Michael,
I appreciate your help.
Herbert