TM1 Forum

Hi,

I have a cube with four dimensions,

lets call them

Dim1
Dim2
Dim3
Dim4 - Measure dimension

In dim 4 there is a measure called DaysTaken

I want to be able to create bandings for DaysTaken, so the following bandings:

Band1 = DaysTaken between 1 and 4
Band2 = DaysTaken between 5 and 15
Band3 = DaysTaken between 15 and 30
Band4 = DaysTaken greater or equal to 30

My thought was to create these bandings as a flag measure in Dim4, the measures dimension and
then have a rule for each band, so for example

[Band1] =N: If( [daystaken] >=1 & [daystaken] <=4, 1,0);
[Band2] =N: If( [daystaken] >=5 & [daystaken] <=15, 1,0);

etc

The problem comes when trying to report off this, so say I have another measure in Dim4 called sales, how do I report the total value for sales where Band1 =1 etc?

update: I do have something that kind of works, so instead of separate measures for Band1, Band2 etc, I created a single measure called Bandings which returns either Band1, or Band2 depending on daystaken. I then created 4 measures which capture sales by banding, so for example:

[SalesByBand1] = N: If(['Banding'] @= 'Band1', ['Sales'],0);
[SalesByBand2] = N: If(['Banding'] @= 'band2', ['Sales'],0);
etc

Maren

Why not add another dimension for Bandings since that seems to be the way you want to be able to slice the data?

Hi PavoGa,

that did cross my mind but that would require the data getting populated via a data load, so it would be a process not a rule? Because if I added in Bandings as a dimension it would mean I would need to add a particular sale to a particular banding, wouldn't it?

Maren

I may have missed it, but how are [daystaken] and [sales] loaded? From another cube via rules?

Either way, seems obvious your measure should be sliced by the Banding. Adding individual measures to achieve this is really defeating the purpose and advantages of dimensional modeling and TM1. What happens if you need a new banding? You'll have to add new measures, modify rules, etc, where if you use a separate dimension, you can achieve what you need by adding a new element and populating whatever attributes or entries in a mapping cube that the rules and/or TI use to facilitate loading the data.

Without knowing your model and requirements, I could not tell you definitely which of rules or TI is better, although if the two measures mentioned above are by TI, then by all means, setup the TI to slice into a bands dimension.

Hi,

The data will be loaded by TI but can be modified by the end user.

The reason I was reluctant to add in another dimension was because if the user wanted to modify the values this new dimension for bandings would add an extra level of granularity for them to deal with.

Maren

To derive the Band, create a standalone dimension with Bands as C Levels and days as N level.
If you only need to know the Band, I.e. 1 through 4 then just use elpar to get the parent and possibly if not zero and dimix is zero then maximum band.

If you change the components of your C levels, your bands will update automatically.

If you are then trying to do a count on how many customers are in Band 1, Band 2 etc. you would need measures for these and then rule derive a zero or 1 based on the Band.