dynamic customer grouping

[manne]

Hi Tm1 group,

I have a question on the following challenge.

I have a cube with monthly customer revenues, the revenues are been generated by branches and over a branch hierarchy been consolidated to the total group results.

In an report ( active form in excel) I will would like to group the total revenues of a branch with the information, how many revenues been with customer of a specific size, e.g.:

> 500k customer = 2.000k
250k - 500k customer = 1.580k

The grouping of the customer should be done on the total group level and is been calculated by taking the YTD results divided by the number of month, multiplied by 12.

I have a played with the idea to run a monthly TI and do this calculation and then add/set a attribute on the customer - but I wanted to see if the comunity has a more dynamic idea via rules.

I hope I illustrated this detailed enough, if not please let me know and many thanks for the assistance

Manfred

[jim wood]

Three nice letters for you.... MDX. There has been a lot of discussion on topics directly related to this over the last couple of weeks. A quick browse / search should give you what you need.

[lotsaram]

To further Jim's pointer I think I would approach this problem with MDX to do the customer size segmentation to separate customers into distinct subsets based on the historical sales in range criteria, and then use the subsets to update a hierarchy so that the segmentation groupings could also be used for summing in an easy manner.