Calculate ratio // Verhältnis berechnen

[Muecke]

Hello, everybody,

I want to determine the sales ratio in a product group.
Challenge: How do I get the right data and calculate in Phocas.

Example:
A = All article numbers (filter product group = Sellotape rolls [result 3.000 pieces])
B = All article numbers that the customer has purchased (filter period = 01.01. to 31.12.2019) [Result 1,545 pieces].

Solution: Sales relationship (assortment coverage)
= B / A

I am aware that I have to do this in custom mode.

But I don’t get A and B created

Can you help me?
or do you have an idea how I can do this?

i want to output this in a diagram (semicircle like a speedometer)

Greetings from Germany

Translated with www.DeepL.com/Translator (free version)

Greetings from Germany
Mücke

[JonKemp]

Hi @Muecke,

Have you looked at percentage share? This will work if you are NOT in custom mode. have a look here: https://helpphocassoftware.atlassian.net/wiki/spaces/userdoc/pages/590053/Format%2Bdata%2Bview

I have raised this on a feature enhancement for custom mode here: % Share - Enhancements - Display, Others and Criteria features

Regards,

Jon

[Muecke]

Hi @JonKemp,

Thanks for the link.

It’s not quite what I’m looking for.

Example:

I have 1,000 part numbers
My customer buys only 895 article numbers from me.

Then the customer has a share of (895 x 100% / 1.000) = 89,50%

I hope people understand what I’m looking for.
Because of the translation from German / English it is sometimes really hard.

Greeting
Mücke

[Nicktool]

If I am understanding this correctly, what you’re asking for is possible through Market Mode.

Market mode will show you what percentage of a selection of products a customer is buying.

For example:
Step 1. I focus on a product segment like Sellotape.
Step 2. Change mode to Market.
Step 3. I focus on the customer I want, and this customer is now listed as the Segment in the top of the screen.
Step 4. I select Product from the yellow menu box above the data.
Step 5. I read from the percent of market column, the percentage of products within this category that my selected customer is buying.

Hope this helps!

[JonKemp]

Hi All,

I will be posting this on my other post regarding % share in custom mode and in tips and tricks. 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

[Muecke]

@JonKemp: Wow, that’s quite an elaboration. I hope to find time to build and test it in the next few days.
I’ll give you a feedbeck afterwards.