Hi
Using the Value Entry table I want to establish a count of New Customers.
The below query will return a count of customers (which is the Source No. field) who have purchased in May. So - ideally I want to filter this query further by excluding any customers who have purchased in the 3 months prior (Feb,March and April).
=NL("CountUnique","Value Entry","Source No.","Brand Code","ABC","Item Ledger Entry Type","Sale","Posting Date",C$8,"Salesperson Code",$B12,"Document Type","Sales Invoice")
Is that possible?
Date
Votes
5 comments
-
Jet Reports Historic Posts Yes, although it gave me a little bit of a headache.
One way of doing this is to use NP("Intersection") and NP("Difference").
First, let's build your filter that will give you the list of every customer in the last month, which is pretty similar to your previous one:
Filter1 = NL("Filter","Value Entry","Source No.","Brand Code","ABC","Item Ledger Entry Type","Sale","Posting Date",C$8,"Salesperson Code",$B12,"Document Type","Sales Invoice")
For the next part I've assumed that there's a date filter giving you the previous 3 months in C$9. We want to get a list of all the customers who ordered in those months:
Filter2 = NL("Filter","Value Entry","Source No.","Brand Code","ABC","Item Ledger Entry Type","Sale","Posting Date",C$9,"Salesperson Code",$B12,"Document Type","Sales Invoice")
To get a list of those who didn't order in that period, you can do use NP("Difference") with a list of all your customers:
Filter3 = NP("Difference",NL("Filter","Customer","No."),Filter2)
Then you can use the NP("Intersection") to get a list of customers who both ordered in the last month, and didn't order in the last 3 months):
Filter4 = NP("Intersection",Filter1,Filter3)
Finally, wrap it all up in a count:
=NL("Count",Filter4)
or
=NL("Count",NP("Intersection",NL("Filter","Value Entry","Source No.","Brand Code","ABC","Item Ledger Entry Type","Sale","Posting Date",C$8,"Salesperson Code",$B12,"Document Type","Sales Invoice"),NP("Difference",NL("Filter","Customer","No."),NL("Filter","Value Entry","Source No.","Brand Code","ABC","Item Ledger Entry Type","Sale","Posting Date",C$9,"Salesperson Code",$B12,"Document Type","Sales Invoice")))
If that's not too long! I did a test of the principles using my purchase header table and my vendors and it seems to give the correct answer. -
Jet Reports Historic Posts Hi
Wow. That is far more complicated that I could ever imagined! Thank you so much for taking the time to assist. Unfortunately this method does not seem to work for me.
I wonder if it is because I am using value entry. Perhaps I should go down the route of purchase header/vendors as per your testing? Would you mind explaining further?
I have attached what I have so far for what it is worth. Your method has been entered into column H. Thanks again. -
Jet Reports Historic Posts I like a jet reports formula challenge, it keeps me on my toes and gives me ideas for my own reports.
I think your dates are the wrong way around. The current month should be in the first part of the formula and the three month one in the second part. I don't know if that will fix it though! If it doesn't, try splitting out the formula into the next few columns and it might be more obvious where the problem is. Put each filter separately, wrapped in a countunique and see what numbers you get out. For example:
=NL("Countunique",NL("Filter","Value Entry","Source No.","Brand Code",$C$4,"Item Ledger Entry Type","Sale","Posting Date",$H$10,"Salesperson Code",$B16,"Document Type","Sales Invoice"))
should match your March figure in column C and:
=NL("Countunique",NL("Filter","Value Entry","Source No.","Brand Code",$C$4,"Item Ledger Entry Type","Sale","Posting Date",$H$8,"Salesperson Code",$B16,"Document Type","Sales Invoice"))
should be the sum of D through F.
=NL("Countunique",NL("Filter","Customer","No."))
should be the same on each line, and probably in the thousands.
If they look fine, then the NP("difference") for 2 and 3, should give you 3-2. At that point the only thing left is the intersection.
Let me know how it goes! -
Jet Reports Historic Posts And the type of table shouldn't matter, unless you find that it's particularly slow.
-
Jet Reports Historic Posts Ah. Thanks again but I think this way could be flawed I'm afraid.
Counting unique customer numbers in any given 3 mth time period would not take into account any customers who have ordered in more than one month. So the overall customer count using this approach would be less than it should be.
Is it possible to take a different approach and somehow do a countunique of customers in any given month (again - May as an example) - and then exclude from this number any Customer No where the sum of 'Sales Amount Actual' is greater than 0 for the 3 months prior? (Feb March and April)
(Would not know where to begin putting all of this into a formula sadly!)
Please sign in to leave a comment.