Calculating Clusters (intervals or ranges) based on a Charactacteristic
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!