topic Re: Interval match on Object level in QlikView

Interval match on Object level

Wed, 28 Mar 2012 08:50:23 GMT

Hi,

I have a requirment like Interval match in Object level, what i mean to say is; i have build an expression in text object to show Score on Dashboard. this resulted Score is link with another table which contains band/Grade.

this could be easily achivable using interval match function in script level but how does it possible on GUI level.

Example :

Reslut number : 69

Score Band : 50 55 60 70 80 90

0 1 2 3 4 5

Expected result should be 2 in a another text object.

Regards,

Interval match on Object level

Wed, 28 Mar 2012 12:16:48 GMT

any help !

Interval match on Object level

CELAMBARASAN — Wed, 28 Mar 2012 12:28:48 GMT

Hi,

Score band is from table?Is it fixed or will change after sometime?

Celambarasan

Interval match on Object level

tanelry — Wed, 28 Mar 2012 12:40:42 GMT

Maybe just make simple expression

if(Score<55,0,

if(Score<60,1,

if(Score<70,2

etc)))

Use calculated variables instead of numbers if needed.

Interval match on Object level

Wed, 28 Mar 2012 13:20:04 GMT

This band is based on diffrent platforms(Base) and can vary. so, we can not hardcode values. it has to be dynamic.

Interval match on Object level

tanelry — Wed, 28 Mar 2012 14:13:06 GMT

Could be solved using calculated variables (vScoreBase0, vScoreBase1)?

if(Score<vScoreBase0,0,

if(Score<vScoreBase1,1,

if(Score<vScoreBase2,2

etc)))

the variables would pick the values from the band/Grade description table...

Interval match on Object level

Wed, 28 Mar 2012 16:11:03 GMT

Weight Band

Base	KPI	0	1	2	3	4	5
a	a1	20	30	40	60	80	90
a	a2	10	35	45	60	80	90
a	a3	25	35	45	60	80	95
b	b1	30	40	50	60	75	90
b	b2	30	40	50	60	80	95
b	b3	20	30	40	60	80	90

there are nearly 3 KPIs for each Bases (bases and KPIs can grow in numbers) , it would be really difficult to create so many Variable to handle all bases with combination of KPI, KPI is the value which i want to display in text object. text object is a just an example, real requirment would be to display Health card in single Table.

Re: Interval match on Object level

Thu, 29 Mar 2012 07:41:43 GMT

these are very very critcal KPIs and cant get through it ..

Re: Interval match on Object level

tanelry — Thu, 29 Mar 2012 09:52:39 GMT

I see, this gets complicated.

I experimented with some dummy data, see attached.

I loaded the grading table based on your example, unpivoted, into a 'data island'.

In a text box the grade is picked for each KPI using expression (example):

=max({<Base={a},KPI={a3}>} if(GradeBand<=$(=calculated_value),Grade))

To represent KPIs in a table, you need to calculate each cell separately, hence many if()'s in the final expression:

if(Base='a' and KPI='a1', max(if(GradeBand<=$(=sum({<Dim={A}>} value1)),Grade)) ,

if(Base='a' and KPI='a2', max(if(GradeBand<=$(=sum({<Dim={A}>} value2)),Grade)) ,

etc

))

Re: Interval match on Object level

Thu, 29 Mar 2012 10:21:17 GMT

thx dear,

i have worked out in a samiler way but as More KPI comes in it is bit difficult to control and gets ugly..

but thnx any ways..