Hi,
I am trying to obtain a value for 'lost' quotes per salesperson. What I am trying to achive is a sum of the last version of quotes, that have an opportunity number where the status is 'lost', and I am trying to do this for a specific salesperson, per month. I am gettin close but my result is double what it should be. I.e. Quote 'A - version 1' has a value of £500, Quote 'A - version 2' has a value of £550, the total I am getting is £1050, when I want £550.
I have tried many different approaches to this, all of which have not given me the correct answer. I have tried creating seperate NL("Filter") and referencing them in an NL("Sum") formula, and I have also tried nested NL("AllUnique") functions within a seperate NL("Sum") formula. I am now currently trying to use "Link=", but it is still not giving me the correct value, although I rarely use the "Link=" function and so am not too familiar with it.
I am none too sure what the best approach here is, so I don't think it is worth showing what I have come up with so far!!! However in essence it is along the lines of:
Sum sales line archive line amount (or amount), where the No. of archived versions = the version no. when linked to the sales header archive, and the opportunity no. = no. when the sales header archive is linked to the opportunity. The opportunity is filtered by;
("Sales Document No.","<>''","Salesperson Code",$D$7,"Status","Lost","Creation Date",D$12)
Does anyone have any suggestions on what is the best approach to overcome this issue? I have looked accross this forum for sometime and found a few examples that I thought may have helped, however in trying to tailor them to my specific needs they have been unsuccessful.
Any help would be greatly appreciated
Regards
Ben
Date
Votes
4 comments
-
Jet Reports Historic Posts Hi Ben,
Your formula sounds complicated, and I am not sure what the correct formula will be. However, it seems to me that you're going to need an NL(Last) with a sort in there or an NL(First) with a descending sort. Of course if you're not replicating out records, you might need to use the NL(Last) as a calc filter. This seems like the piece you're missing in order to only get the last quote rather than the sum of all the quotes. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
In my first attempt at trying to solve this I did use the NL("Last") function as a nested function, but it took the last value from all the results (obviously). I.e. if there was a quote that had 5 versions, it would use that as the version number, so would exclude quotes that may only have had 1 version.
I think your idea of using NL("last") as a calc filter sounds more plausible so I think I may try that.
Thanks for your help
Regards
Ben -
Jet Reports Historic Posts Hi Ben,
I'm looking for the exact same solution that you describe in your post. We're implementing ProjectPro for a customer and there's a 'Job Forecast' table where I want to get the sum of the most recent (based on a status date field) forecasted amounts per job task no. I can get the data if I list out the job tasks individually and use the NL("Last") formula for each one, but ideally I'd like to get a single sum of all those values without having to do that. I'm totally stuck on how to compose the Jet formula and would be interested in whether you were able to come up with something that worked for your situation.
Regards,
Rob -
Jet Reports Historic Posts I can not test your report as I don't have data in these tables, sorry.
Please sign in to leave a comment.