TopN/Rank calc?


I’m looking for a way to dynamically filter for top customers for each of my stores. Ultimately, I want to provide what % of sales those top customers represent for each location and overall company. A customer can be top ranked in one store, or multiple stores.

Is there a way to do a TopN function in Phocas, either in custom mode or thru some built in function I’m overlooking?

1 Like

Hi @Mike
have you tried the following:

  1. Create a matrix for the stores and customers:
    1.1 Go to the stores dimension, highlight your stores and click on matrix
    1.2 Go to the customer dimension
  2. Change the Format button (Displaying “Actual”) to % share?

Kind regards

Hi Nicolas

Thank you for the suggestion. That method works well when I want to manually sort for the topN (lets use 25) in each location. I’m looking for a way to do this dynamically, and in summary for dashboarding/kpi purposes. For example: Store “A” had sales last month totaling $10M. The top 25 customers for Store A (ranked by sales last month) totaled $6.5M, or 65% of sales. However, Store B’s top 25 might only represent 45% of its sales. Also, Store B’s top 25 would likely be a completely different list of customers than Store A’s list.

The only way I can do this today (that I know of) is to nest location/store by customer (or visa versa), export to Excel, and then using a series of pivot filters and functions. Unfortunately, that means it can’t be summarized in a Phocas Dashboard.

@Mike another work around is this:

  1. select the top 20 → Right click → exclude selections
  2. Click on cogwheel and tick the box called “show others”.
    It will now show you your top 20 as “others” (and you will get % of all) and then all the rest. you can then turn it into a pie chart.
    The only limitation is that it can’t be dynamic - you will have to select your top 20 every month (once the month is over).
    This way might still be quicker than download into excel and pivot.
    Hope this helps.
1 Like

Thanks @lina.tamkunaite ,

I’m really looking for a way to dashboard the results. To your point, the top 20 would have to be manually filtered each month. Plus, I can’t find a good solution for the variation in customers by location. I would have to create a separate top 20 for each location.

I will add this to the product suggestions section