Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

May be this:

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

Not applicable
Author

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

sunny_talwar

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
Author

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

Not applicable
Author

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

sunny_talwar

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.