Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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! 🙂
Try this instead
Sum(Aggr(
If([Lower Bound] < $Variable and [Upper Bound] > $Variable, Final Score, 0)
, [Model ID], [Lower Bound]))
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! 🙂