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: 
swetasingh
Partner - Contributor III
Partner - Contributor III

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 IDLower BoundUpper BoundFinal Score
AA0.250.52
AB0.50.754
AA0.7515
AB11.257
AA1.251.58
AB1.51.7510
AA1.75211
AB22.2513

 

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 IDLower BoundUpper BoundFinal ScoreCalculated
AA0.250.522
AB0.50.754 
AA0.7515 
AB11.257 
AA1.251.58 
AB1.51.7510 
AA1.75211 
AB22.2513 

 

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 (2)
1 Solution

Accepted Solutions
swetasingh
Partner - Contributor III
Partner - Contributor III
Author

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

View solution in original post

2 Replies
sunny_talwar

Try this instead

Sum(Aggr(
  If([Lower Bound] < $Variable and [Upper Bound] > $Variable, Final Score, 0)
, [Model ID], [Lower Bound]))
swetasingh
Partner - Contributor III
Partner - Contributor III
Author

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