Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Nested set analysis: show customers with quotation but not orders

Dear fellow Qlikview users:

I'm working on a pivot table that is supposed to show:

- Customers who have received quotations from my company but who have not placed an order

Customer.CUSTOMER_ACCOUNT = customer number who identifies the client and is used for further analysis

Sales_Header.ORDER_TYPE = either "Sales Quote" or "Sales Order". We're using this in the nested set analysis to show only those accounts for who there is not "Sales Order" in the system.

The expression I'm using is:

Only({$<Customer.CUSTOMER_ACCOUNT = {$(Count({<{Sales_Header.ORDER_TYPE={'Sales Order'}>}Sales_Header.ORDER_TYPE) = 0)}>}Customer.CUSTOMER_ACCOUNT)

Would be great if someone has an idea. I've experimented a lot, but cannot get it to work.

Thanks.

Dennis

7 Replies

Re: Nested set analysis: show customers with quotation but not orders

May be this:

Only({$<Customer.CUSTOMER_ACCOUNT = {"=Count({<{Sales_Header.ORDER_TYPE={'Sales Order'}>}Sales_Header.ORDER_TYPE) = 0"}>}Customer.CUSTOMER_ACCOUNT)

Not applicable

Re: Nested set analysis: show customers with quotation but not orders

Thanks, Sunny!

The expression still returns a zero. Even with the additional "= and " in the expression.

If I simplify the expression, for example:

Count({$<Sales_Header.ORDER_TYPE = {'Sales Quote'}>} Distinct Customer.CUSTOMER_ACCOUNT)

or

Count({$<Sales_Header.ORDER_TYPE = {'Sales Order}>} Distinct Customer.CUSTOMER_ACCOUNT )

hen there is data and I could reconcile the lists in Excel. That's not the point of course. Still think something is wrong with the nested expression.

Any idea?

Dennis

Re: Nested set analysis: show customers with quotation but not orders

I see an error in set analysis syntax

Only({$<Customer.CUSTOMER_ACCOUNT = {"=Count({<{Sales_Header.ORDER_TYPE={'Sales Order'}>}Sales_Header.ORDER_TYPE) = 0"}>}Customer.CUSTOMER_ACCOUNT)

See if removing this resolves the issue

Only({$<Customer.CUSTOMER_ACCOUNT = {"=Count({<Sales_Header.ORDER_TYPE = {'Sales Order'}>} Sales_Header.ORDER_TYPE) = 0"}>} Customer.CUSTOMER_ACCOUNT)

Not applicable

Re: Nested set analysis: show customers with quotation but not orders

Hi Sunny,

Not yet. I will look for a way to upload part of the app. Think it's difficult to troubleshoot otherwise.

Thanks!

Dennis

Re: Nested set analysis: show customers with quotation but not orders

Not applicable

Re: Nested set analysis: show customers with quotation but not orders

Hi Sunny,

Thanks for the very helpful links. I am uploading a sample. The chart on the right shows customers, quotes and orders separately. The one on the left is the intended format. It's supposed to show clients who have received quotes but have not placed an order. They should have x number of "Sales Quote" and 0 number of "Sales Order". If you can make time to take a peek - great. I had to scramble some data, but you should get the picture.

Thanks.

Dennis

Re: Nested set analysis: show customers with quotation but not orders

The chart on the right has date as a dimension and other one uses Supplier account. How is the comparison made? I mean can you share what is the desired output? Numbers you expect to see would help a lot.