Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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;