topic Class with variable intervals in QlikView

Class with variable intervals

zagzebski — Tue, 19 Jan 2016 22:37:18 GMT

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

Re: Class with variable intervals

swuehl — Tue, 19 Jan 2016 22:43:14 GMT

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.

Re: Class with variable intervals

zagzebski — Tue, 19 Jan 2016 23:30:23 GMT

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

Re: Class with variable intervals

Wed, 20 Jan 2016 02:39:58 GMT

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;