Percentage share in Custom Mode

[JonKemp]

Hi All,

Nifty trick to get percentage share to work in custom mode. Below are the steps to produce:

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()) * 100
This 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

[dphelmken]

Truly great stuff here @JonKemp. I found myself getting frustrated trying to figure out a direct method of getting to this type of calculation, and your solution worked perfectly.

Thanks for sharing!

[Jordon]

wow! this is epic! Is there any documentation anywhere on all the commands we can use? I’ve only been able to find them by chance here in PUG so far…

[JonKemp]

Hi @Jordon,

Not that I know of. I raised this when we had a meeting with Phocas and they let me know about this. I think there will need to be some trial and error with the “^” option as I am sure there are other use cases out there.

Kind Regards,
Jon

[alexlovenberg]

@JonKemp This was super helpful! The calculation works great to show the % of company sales for each customer. It gives me all the info I need when I am in grid mode.

However, I am trying to use this as a summary widget on my Customer scorecard, so that when I filter by a customer, I see their share of company sales as a %. When I filter with the current set up, it always says 100% (understandably since the formula is not customer specific). Does anyone have a solution?

[JonKemp]

@alexlovenberg ,

Not that I know of. This “hack” allows you to inspect the entire dataset so will always be the sum of the whole and compare to row. AS soon as you want to pinpoint a row this is where you will come unstuck…unless there is another way that someone know.

Regards,

Jon

[Linh]

Great step by step explanation, @JonKemp !
Thank you

[dan.klob]

This. is. gold. Major kudos to @JonKemp for sharing this up.

[oliver.fleming]

Thought I might also share a similar solution for a slightly different but relevant use case.

Let’s say that rather than percentage share, you want to find out what “percentile” an entity falls into, eg. which products are in your top 90%, 80%, 70%… best performers

You can follow the same steps that Jon has laid out, and then use the following query in your calculated column:
^PERCENT_RANK() OVER (ORDER BY {a} ASC) * 100

The result should look something like this:

Also, if you prefer your top performers in the single digits, eg. 1st percentile, simply swap ASC with DESC to change the order:
^PERCENT_RANK() OVER (ORDER BY {a} DESC) * 100

Hope this helps!