Hello there, thanks for responding, I do, with a couple of rogue exceptions which i should be able to filter out, it's Inventory Item.

Hello there, this has worked perfectly, and i can use this in quite a few upcoming reports for Q2, thanks ever so much to you both for your help and patience with this, much appreciated.

Hello there, it worked! Thanks ever so much, my only issue now is i need to specify in the criteria that the new value is an expected date after Q1, as some of the new values are showing a new value still within this quarter, andf my aim is to show only those instances where an Opp that was in Q1 at the start of the year…

Hello there, thanks for getting back to me, i tried the formula as above and it returned an unexpected error, i tried it both within my existing search and i also replicated the above exactly and the same message came back, so not sure what's going on there.

Hello there, apologies for the late reply, have been on leave, looking at the above formula, i'm not sure how to apply this as a criteria field, it looks more like a column formula, am i missing something? Thanks

Hello there, apologies, on annual leave, as mentioned, once i'd applied your suggestions and the anomalous results still appeared, i realised that this was solely due to both the itemised and un-itemised subtotals in the Sales order record, once these were eliminated in the criteria the formulas worked correctly across all…

Hello there, thanks for getting back to me with the solution, fixed the lower case issue and tried the formula across all types, and it works perfectly, very grateful for fixing what has been a very intractable issue for us, much appreciated.

Hello there, thanks for your help with this, appreciate it's a bit of an odd request.

Hello there, thanks for following up, i did manage to fix the other anomalous results, as well as the Subtotals there were further subtotals in some Sales orders but without Subtotal as a heading, (as an item, so to speak) so had to find a way of eliminating them, which was tricky as it was literally just a subtotal of…

Hello there, apologies, have been off sick, i did as directed this morning and in the process realised that the reason for the anomalous figures mentioned was that it was pulling in subtotals from the Sales order at item level, once eliminated in the criteria the correct billing figures pulled based on the formulas you…

Hello there, thanks for getting back to me, i am using a saved search using system notes, what i'm struggling with is a way of setting a criteria for the Old Value (expected close date), so that it only counts old value expected close dates with a date in Q1, if i can figure that issue out the rest of the Saved search as…

Hello there, thanks very much for the suggested solution, this does seem to be working correctly on many of the Sales orders with multiple Invoices, however there's still some anomalies, e.g. the example highlighted in yellow below, have double checked and is doubling the actual amount billed, the one in red the actual…

Hello there, thanks for getting back to me, please see below for related records, and added fields in drill down, thanks.

Hello there, thanks for getting back to me, i have looked at the two above, but not really relevant unfortunately, looking for a search that shows when an Opp that was due to close within Q1 as the Pipeline stood on 1st January, has been moved to Q2 or beyond since the 1st January, i did build a saved search that did this…

Hello there, thanks for getting back to me, and understood, i've added in screenshots of the Sales Order and Invoice relating to the first one highlighted (Amount Billed: £6158). It appears that the anomalous result thrown up by the suggested formula for Amount Billed is multiplying the actual billed total by 6. (6 x 6158…

Hello there, thanks for getting back to me, the examples are not unique, a majority of the results are inflated. As i mentioned when i first asked for help, the original formulas i used are working perfectly well when there's just one invoice related to a Sales order, so i think i'm just in need of an adjustment to the…

Hello there, thanks for getting back to me, unfortunately these haven't worked, both of them inflate both the amount billed and unbilled amounts, as an example two results below where i know there was only one invoice raised against the Sales Order, the results circled in blue are correct as per my original formulas, the…

Hello there, thanks for the message, i haven't unfortunately, no progress despite some considerable effort but as i detailed before, it's only partially working, and will only show one result for GP% at a time, dependent on which item type i select in the drop down filter, would appreciate any advice, thanks.

Hi Dominica, thanks for getting back to me, have tried these and not functioning as they should, the amount billed is inflated in some cases and the amount unbilled is showing amounts where the Sales order has long since been fully billed, i do know that the two formulas i had before were functioning perfectly well on all…

Hello there, sorry, just to follow up, have built out the report despite the issues, it's an attempt to measure GP% by item type/revenue stream, but complicated slightly that our services need to be split out into our company Support contracts and installation Services, hence the bottom set of formulas, the GP totals are…

Hello there, thank for getting back to me, i have tested the formulas in isolation, i.e {estgrossprofit})/SUM(NULLIF({amount},0)) END and no problems, pulling correct data, the problem appears to be the CASE WHEN summary type combination, i did try the combination highlighted below and it pulled the correct GP% for…

Hello there, hope you're well, and sorry for the very late reply, i've been bogged down in other reports for the last few weeks, re the above I managed to get the results i wanted via another manufacturer grouping method in the end, but am still wrestling with this formula type on another saved search, and simpler in terms…

Hello there, thanks for getting back to me, i have tried that and has come back as 'Unexpected Error', and if i use SUM i get wildly inflated results, i would think there is a way round it along the lines your suggesting but i wouldn't know how to configure the Parens in such a way to make it function as needed.

Hello there, sorry to be asking again, but would be very grateful for any pointers to the issue in the message directly above, the billing transaction fields work well but just need to find a way of adapting the two formulas as mentioned, thanks for your help.

Hello there, am putting the summary of my query out again as have tried a few solutions and didn't want to lose the thread of my original request, have created the search below to pull the net of tax value of SO, billed amount, and unbilled amount, all additional fields and the SO total are working correctly, and the two…

Hello there, aye, i think i'm going to have to persevere with what i have given the previous formula didn't work for unbilled, out of interest where are you getting billed amount from in the above? Thanks.

Hello there, thanks for the suggestion, tried the above but it pulled a lot of incorrect and pretty wild results, the frustrating thing is both the formulas i have are pulling everything correctly where there's a single invoice against a Sales Order, across multiple results, this only breaks down when more than one invoice…

Hi there, thanks very much, much apreciated, i've tried a few different combinations of summary type on both formula and in the column summary type but no luck as yet.

Hello there, and thanks for getting back to me, unfortunately after checking i realised both the Billed and Unbilled amount formulas are not pulling in correct amounts on those instances where more than one invoice has been raised against a Sales Order, they're both pulling the highest value invoice only, would be grateful…

Hello there, to follow up, i think i may have fixed it, i used the formula below with Summary Type set at MAX also and looks to be pulling the correct results, will double check, thanks. (MAX{totalamount}-MAX(NVL({taxtotal},0)))-(MAX{billingtransaction.totalamount}-MAX(NVL({billingtransaction.taxtotal},0)))