topic Re: Calculated Field in QlikView

Calculated Field

Wed, 19 Mar 2014 19:30:02 GMT

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

Re: Calculated Field

Anonymous — Wed, 19 Mar 2014 19:41:26 GMT

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.

Re: Calculated Field

Wed, 19 Mar 2014 19:42:39 GMT

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

Re: Calculated Field

Anonymous — Wed, 19 Mar 2014 19:44:52 GMT

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

Re: Calculated Field

Wed, 19 Mar 2014 19:54:18 GMT

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

Re: Calculated Field

Anonymous — Wed, 19 Mar 2014 19:58:46 GMT

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

Re: Calculated Field

Wed, 19 Mar 2014 20:11:46 GMT

Thank you Michael,

I appreciate your help.

Herbert