Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
mov
Esteemed Contributor III

Re: Calculated Field

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

6 Replies
jsaradhi
Valued Contributor

Re: Calculated Field


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

Re: Calculated Field

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

mov
Esteemed Contributor III

Re: Calculated Field

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

Re: Calculated Field

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

mov
Esteemed Contributor III

Re: Calculated Field

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

Re: Calculated Field

Thank you Michael,

I appreciate your help.

Herbert

Community Browser