topic Re: Interval Banding in QlikView

Interval Banding

kevbrown — Thu, 02 Apr 2015 08:59:19 GMT

I need to create bandings in my bar chart however all the intervals are different. For example. I have a field that i want to count and put into bandings of - 1,2,3,4,5,6,7, 8-14, 15-21, 22-28, 29 days - 6 months, 6 months - 12 months and > 12 Months

How do i do that?

Kev

Re: Interval Banding

datanibbler — Thu, 02 Apr 2015 09:03:25 GMT

Hi,

you can use the IntervalMatch() function for that.

Look it up in the help_file.

It's actually a two-step approach:

- First you LOAD a little inline_table holding your intervals (with the IntervalMatch() prefix)

- Then you can join those intervals to your actual fact table.

=> Then you can use the interval as a dimension in a barchart or whatever.

HTH

Re: Interval Banding

buzzy996 — Thu, 02 Apr 2015 09:03:33 GMT

u can define based on ur conditions one if else case in ur script and label as entire if else statement under one label and u can use that label as ur dimension in ur bar chart.

Re: Interval Banding

Thu, 02 Apr 2015 09:04:48 GMT

hi,

Resolved it by using an if condition in the load script

Re: Interval Banding

Thu, 02 Apr 2015 09:17:10 GMT

Kevin,

This can be achieved using IF statement or Interval Match.

IF Statement:

If(FieldName)<=BandValue1,'BandValue Name1',

If(FieldName)<=BandValue2,'BandValue Name2',

If(FieldName)>BandValue3,'>BandValue Name3'))) as [BandingField],

Use logical operator depending on you requirement.

Interval Match:

[Banding Table]:

LOAD * INLINE [

Banding Group, Start, End

Use Band range values here

];

IntervalMatch(FieldName)

load

Start,End

Resident [Banding Table];

Regards,

Sridhar

Re: Interval Banding

kevbrown — Thu, 02 Apr 2015 14:40:50 GMT

Thanks, the IF statement works. Just a quick question. For 1,2,3,4,5 it works fine. What if I want banding for 8 days to 14 days for example. So I'd have 1,2,3,4,5,6,7, 8-14 etc

Kev

Re: Interval Banding

Thu, 02 Apr 2015 15:51:39 GMT

Kevin,

use the below statement:

If(Ceil(FieldName)>=8 and Ceil(FieldName)<=14, '8-14BandValue Name') as [BandingField]

use Ceil function if necessary.

Regards,

Sridhar