Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this:
Only({$<Customer.CUSTOMER_ACCOUNT = {"=Count({<{Sales_Header.ORDER_TYPE={'Sales Order'}>}Sales_Header.ORDER_TYPE) = 0"}>}Customer.CUSTOMER_ACCOUNT)
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
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)
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
I agree. Look here for help with uploading
Preparing examples for Upload - Reduction and Data Scrambling
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
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.