All,
I had a meeting with Phocas yesterday and they have shown me that this indeed can be done!
Its a little different to normal custom mode calculations, so please bear with this and please read till the end of post:
Scenario
I want a custom mode widget which shows the percentage share of the dimension I have selected. So to keep it easy, lets say I want to see % share across all branches:
The custom mode criteria is this:
So far so good…
Challenge
Because I am in Custom mode I want to see this percentage share. I.e. What is the overall percentage of one branch over the other. So in the case of GB, the formula would need to be 350,265 / 474,032 = 73.89%.
Normal custom mode calculations cannot work out a value based on the current dimension. This is not the case! See below:
So how is this done? Firstly we need to understand the building blocks to construct the value.
How do I get the overall total to appear on each line, in this example, how do I get 474,032 on each line?
If we refer to standard SQL the typical syntax would be SUM(myfield), now because this is Phocas NOT SQL the language is a little different:
SUM({a})IMPORTANT: the “{” is paramount for this to work so don’t skip them out
This however is only the first part and on its own and it will not work…yet, to get it to work we need to expand this to be:
SUM({a}) OVER()
Why Over?
Over is a command that is grouping based on a specific value, since its “()” we are saying group EVERYTHING.
This will give you the following:
Now if you have understood the above (basically saying give me total value of {a}, then the final bit is easy which would be to say with the total value ascertained divide it bay the current rows (dimensions) value:
{a}/SUM({a}) OVER()…and voila! You now have the percentage share (albeit not nicely for formatted yet!):
Finally lets get this looking nice, so change the formula:
({a}/SUM({a}) OVER()) * 100This now gets it displayed correctly:
Success! Now if you are anything like me, you may have reading this post and jumping the gun and getting this error:
That’s because there is a special symbol that we need to add to let the magic happen. This is “^”. So the full syntax is:
^({a}/SUM({a}) Over()) *100
The “^” breaks out of standard Phocas syntax and uses SQL commands instead. There is another post I put on a while ago which talks about using case statements on the calculations here
Pretty nifty! I for one am happy this can be performed!
I hope this helps all you Phocas users out. Thanks to @nicolas.servouse & Nejah for their assistance in this fantastic workaround.
Regards,
Jon