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

Nested set analysis: show customers with quotation but not orders

Tue, 21 Feb 2017 12:00:12 GMT

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

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

sunny_talwar — Tue, 21 Feb 2017 12:17:33 GMT

May be this:

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

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

Tue, 21 Feb 2017 14:22:03 GMT

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)

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

sunny_talwar — Tue, 21 Feb 2017 16:30:30 GMT

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)

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

Wed, 22 Feb 2017 14:22:08 GMT

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

sunny_talwar — Wed, 22 Feb 2017 14:58:12 GMT

I agree. Look here for help with uploading

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

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

Thu, 23 Feb 2017 16:03:56 GMT

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

sunny_talwar — Thu, 23 Feb 2017 17:30:30 GMT

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.