Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Interval match on Object level

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,

9 Replies
Not applicable
Author

any help !

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

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

Celambarasan

tanelry
Partner - Creator II
Partner - Creator II

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.

Not applicable
Author

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

tanelry
Partner - Creator II
Partner - Creator II

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...

Not applicable
Author

Weight Band


BaseKPI012345
aa1203040608090
aa2103545608090
aa3253545608095
bb1304050607590
bb2304050608095
bb3203040608090

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.

Not applicable
Author

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

tanelry
Partner - Creator II
Partner - Creator II

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

))

Not applicable
Author

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..