Calculating Clusters (intervals or ranges) based on a Charactacteristic

[Former Member]

Hello Experts,

There is a requirement to show Sales Volume of a product per Net Price (available as Characteristic). This means that in a chart, prices would be shown on the x-axis and the sales volumes on the y-axis. But as within the year there are many different prices for a product, there is a requirement to group prices in 20 clusters (intervals) to reduce complexity. So there will be the following clusters for example:

Cluster 1: 5.00 to 5.10 EUR

Cluster 2: 5.20 to 5.30 EUR

Cluster 3: 5.40 to 5.50 EUR

...

My idea is to create 20 restricted Sales Volume key figures (one for each cluster) that are filtered by the Net Price interval (cluster width) each. For example:

KF 1: Sales Volume, restricted by: Cluster width

KF 2: Sales Volume, restricted by: Cluster width *2

KF 2: Sales Volume, restricted by: Cluster width *3

The width would be calculated by using the following formula:

(Maximum Price - Minimum Price)/ 20

This can probably only be realized by user exit variables as the clusters depending on the selected product have to be calculated dynamically during query runtime.

Do you have another idea how to realize the requirement? If there is no other way, how would the user exit look like? Would I have to use one interval variable per Key Figure? Or would I have to use two single value variables per Key Figure (Variable 1: from, Variable 2: to)

Thank you very much for your help!

Best regards,

Marco

[Former Member]

Hi,

One more option is to add characteristic "Cluster" in the data provider (ODS/CUBE) and while dataloading populate cluster value in this, i.e. if Net Price is 5.00 to 5.10 EUR then Cluster = 1, if net price is 5.20 to 5.30 EUR Cluster = 2 ......... In this way you can simply use Cluster in X axis and Volume in X asis.

Regards

SSS

[Former Member]

Hi SSS,

Thank you very much for your input. I have already been thinking about your solution as well. But the problem is that the solution would be very static as the cluster would be assigned to fixed values. This would make it difficult for products with completely different price ranges (e.g. Product A: Min price 100 EUR, Max Price 120, Product B: Min Price 2,50 EUR, Max Price 4, 10 EUR) . So I think it would only be possible to determine the clusters dynamically in the frontend based on variables, as previously mentioned. Has anybody an idea how to realize it?

Regards

Marco