Is there anyway to calculate what the variance of a row is from the total of the whole column?
Here’s my example:
I want to see what the variance is from the % share columns to the heading, E.g. Line 1 is up by 13.95 but line 2 is down by 8.99
This is in Customer Mode:
Hey JMills, I have been trying to come up with an answer for you but I feel like I am not understanding the question.
Is it possible for you to provide some more information? Even some dummy data showing what you are trying to accomplish?
This currently can’t be done dynamically in Phocas. You would need to get the value and hard code it in the expression e.g. ((B/A)*100)-33.23 which would be incorrect as soon as you made selections.
It would be great if matrix share mode could be setup like you have your custom mode. They’re close but obviously matrix share doesn’t include the value and the total without a matrix. I’ll put forward a product suggestion for that and a “variance to total”.
The screenshot above shows what I’m looking for. The total line shows what the percentage of sales each product category represents. From the total I want to compare each customer’s percentage of sale so I can see how far away they are from the total piece. This will show me customers who aren’t buying the same % as the best customers. In SQL I would nest this total as a left join and then do a variance from that.
Thanks Will. Hardcoding it the only option I could fine too. This then leads to us having to download it as Excel and manipulate it from there.
Hang on a mo, I may be barking up the wrong tree, please ignore if I am not understanding your inital requirement. Wouldn’t this get you what you need?
It may be nasty custom code but think its dooable.
Regards,
Jon
@JonKemp good point. Yes it would be possible by dropping into “nasty custom code”. However this functionality is not documented (see Custom Mode) and its use has limited support.
I think the column expression would be something like:
^((sum({b}) over()/sum({a}) over())*100)-(({b}/{a})*100)
@JonKemp I did read your article first actually but it error’s out when I attempt to do what I’m looking for. If I use @will.marriott formula I get: “An error has occurred when querying the data”
I tried my own but still get the same results.
Hi @jmills ,
Difficult to support without being on your system. Have a look at the error logs (if you can) as this should tell you what happened to some degree.
When I was struggling with it…and I did … a lot 
.I would suggest that you break the formula down, using Will’s code, do the first part which is:
^Sum({b}) over()
See what you get, if that works then add the next bit and the next until whammo you get the error. I also will point out that the error could be a DIV 0 error, dividing by zero. I often put this in as well to error proof which is :
NULLIF({a}.0) which this does is makes the returning value become null if its zero, this stops div 0’s in thier tracks 
Here is one I just did for you:
FYI I copied and pasted Wills code and it worked for me so, I am guessing its Div 0.
Good Luck!
@JonKemp @will.marriott Thank you Sirs! I have this working now thanks to NULLIF’s:
^((sum(NULLIF({b},0)) over()/sum(NULLIF({a},0)) over())*100)-((NULLIF({b},0)/NULLIF({a},0))*100)
Congrats!!! 
Only problem is you are now “unleashed” into the wild world of custom mode and will be looking for places to use this undocumented feature LOL 
Jon
I’m looking for something similar, i,m trying to add Invoice Qty and On Picknote Qty together and then be able to tell me what percentage was invoiced of the total figure, the idea behind it is to see what percentage the warehouse failed to invoice in a day. Any help will be appreciated.
Hey @richard.hodgkisson ,
I don’t think you need a statement to accomplish what you’re looking for. You should just be able to add a custom calculation in Custom Mode with the formula Invoice Qty / (Invoice Qty + On Picknote Qty). To show as a nice percentage you might just have to multiple that total by 100.
I do something similar for sales order conversion%:
Tim
