Qlik Community

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

Accepted Solutions
Partner
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! 🙂

View solution in original post

2 Replies

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

Try this instead

Sum(Aggr(
  If([Lower Bound] < $Variable and [Upper Bound] > $Variable, Final Score, 0)
, [Model ID], [Lower Bound]))
Partner
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! 🙂

View solution in original post