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

[swetasingh]

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)

[sunny_talwar]

Try this instead

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

[swetasingh · Accepted solution]

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! 🙂