Skip to main content
Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculated Field

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

6 Replies
Anonymous
Not applicable
Author


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.

Not applicable
Author

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

Anonymous
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

... 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.

Not applicable
Author

Thank you Michael,

I appreciate your help.

Herbert