% Share - Enhancements - Display, Others and Criteria features

[JonKemp]

Hi,

A user requested today that they want to see products which have a top n% and show the others as a % total. Currently when we use the % share and others together we have this:

As you can see, row one (which was 70%) is now showing 100% (understand as its one row, so it would naturally show 100%), but if in Percentage share format, the user was expecting 70% and the “others” column to be 30% not the actual value, this is what we were expecting:

Can % share please have the ability to show the percentage in “others” instead of the value?

In addition, the end user also wanted in a separate column to see the value, So Ideally the grid wanted to appear as below (I cannot see how this can be achieved in custom mode or database design either as its dynamic based on dimension selection):

Note: For clarity, Column1 is percentage share, Column2 is actual sales value in $

Finally, the absence of criteria to apply to % share. If I wanted to filter on anything above 65%, this is not possible either, as below there is no % share option in the format of the advanced search.

Please can the above be introduced? If there are any workarounds I would appreciate them.

Thanks Jon

[StuartH]

1. I presume the issue with the percentage against “Others” is that it’s technically not part of the grid and so not part of the Share Calc.

One way Phocas might fix this is an Option tick to include/exclude “Others” in the Share Calc.

2. An alternate option which also has other benefits is to allow you to group lines and assign an alias. I’ve had systems in the past that allowed this. Basically pick a few customers and call them Group A, pick another set and call them Others etc. That could aggregate the entries into one line but as it would still live in the grid it would be part of the calc.

[JonKemp]

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()) * 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]

I’m finding that this works out really well with only one level of granularity, but I was curious if you had any thoughts on how to apply this same methodology in a nested grid.

Essentially I’m trying to get the % of sales by customer for individual salesmen, rather than the % of sales by customer out of company-wide sales.

I experimented with using group by in the clause above but was unsuccessful, any additional thoughts would definitely be appreciated!

[JonKemp]

@dphelmken,

Its possible but nasty. The challenge you have is the dimension is not available to latch on to for example the “Company” dimension I used for the previous example would be how I would initally want to partition by (if I was in SQL). So for example the logic would be:

^SUM({a}) OVER()
Would need to be in SQL:
^SUM({a}) OVER(PARTITION BY Company)

Problem is Phocas has no concept of the dimension, i.e. “Company” does not exist in the measures. I did think a little out of the box and was able to break the value up by using another measure which needs to be unique, have a look at the screenshots:
Top level:

Look at “Revised with Granularity” notice this now mirrors the original measure of Order Qty (but this is using partition), now lets see when I use nesting:

Notice how the “Revised with Granularity” is now instep with the nesting, with one exception I will discuss below…

The “nasty” part is we need a means of uniquly grouping by a summed value Not the dimension. I would therefore be looking at something like #Number of records so when added together the data collisions are minimal. For example in my results above look at the “yes” group notice it goes wrong, specifically on GB,NL,PT & ES, thats because the count is 0 on all 4 of them, this is a collision, so it prooves it is working BUT MUST BE UNIQUE values to partition on.

Here is the code I used:

^SUM({a}) OVER(PARTITION BY {b})

Hope this makes sense and is somewhat useful!

Regards,

Jon