Class with variable intervals

[zagzebski]

So I have a simple class function for a calculated dimension which works fine:

Class(aggr(Sum([Amount Revenue Est BoB]),KeyBoBBStratification),5000)

However what happens if I don't want the increments to be 5,000 but actually need intervals like this:

0 - 5000

5,000-10,000

10,000-25,000

25,000-50,000

Greater than 50,000

[swuehl]

Then you can't use the class() function, you would need a set of embedded if() statements (if you need to use the calculated dimension), or use an INTERVALMATCH  with a custom bucket table in the script.

[zagzebski]

Thanks for verifying.  Already tried the if statements but was taking too long for the end users to refresh and need the felxibility to have the buckets change based on filtered data so can't pre-code in the script.

Appreciate the feedback.

Zag

[#4]

You can use IntervalMatch capability to create the custom buckets. IF function may degrade your load performance. So go with IntervalMatch function.

TempBuckets:

LOAD * INLINE [

START, END , BUCKET

0, 5000, '0-5000'

5001, 10000, '5001-10000'

10001, 25000, '10001-25000'

25001, 50000, '25001-50000'

];

TempBridge:

IntervalMatch(FieldName)

Load START. END Resident TempBuckets

;

Left Join(TempBridge)

Load * resident TempBuckets;

DROP Table TempBuckets ;

Left Join(FactTable)

Load

     FieldName,

     Bucket

Resident

     TempBridge

;

DROP Table TempBridge;