New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save \$400. Learn More Partner

Formula works in Table but not in KPI even after using aggregate.

Hi, I have a table loaded in Qlik as Tab 1, after implementing the formula, my values are coming as Tab 2 , so the formula works fine in a Table but when i use the same formula for aggregation, it shows zero.

Tab 1 :

 Model ID Lower Bound Upper Bound Final Score AA 0.25 0.5 2 AB 0.5 0.75 4 AA 0.75 1 5 AB 1 1.25 7 AA 1.25 1.5 8 AB 1.5 1.75 10 AA 1.75 2 11 AB 2 2.25 13

Formula = if( Lower Bound < \$Variable, and Upper Bound > \$Variable, Final Score, 0), where \$Variable is complex value coming from lots of calculation.

Lets say \$Variable is 0.33, the TAB 2 is

 Model ID Lower Bound Upper Bound Final Score Calculated AA 0.25 0.5 2 2 AB 0.5 0.75 4 AA 0.75 1 5 AB 1 1.25 7 AA 1.25 1.5 8 AB 1.5 1.75 10 AA 1.75 2 11 AB 2 2.25 13

When I implement the same formula in KPI as Formula 2 it does not work. Also, I tried putting Formula 1 as another variable and then using in aggregation but it still doesn't work. Please help on the same.

Formula 2 :

Sum(aggr(Formula 1), Model ID)

Labels (3)

• Variables

1 Solution

Accepted Solutions Partner

Re: Formula works in Table but not in KPI even after using aggregate.

Sum(Aggr(
If([Lower Bound] < \$Variable and [Upper Bound] > \$Variable, Final Score, 0)
, [Model ID], [Lower Bound], [Upper Bound]))

I used the formula after some tweaks it worked. Thanks! 🙂

2 Replies MVP

Re: Formula works in Table but not in KPI even after using aggregate.

Sum(Aggr(
If([Lower Bound] < \$Variable and [Upper Bound] > \$Variable, Final Score, 0)
, [Model ID], [Lower Bound])) Partner

Re: Formula works in Table but not in KPI even after using aggregate.

Sum(Aggr(
If([Lower Bound] < \$Variable and [Upper Bound] > \$Variable, Final Score, 0)
, [Model ID], [Lower Bound], [Upper Bound]))

I used the formula after some tweaks it worked. Thanks! 🙂