Rank function set expression not working

[Noor51]

There a problem with this set expression in that the ranking is duplicating, example is it show 11 shows 3 times. I think this might be a decimal issue but not sure. Any help will be appreciated. 

Rank(Count(DISTINCT [Rep Name]) -
(
// RTA Sales % Change
Rank(
(Sum({<[Quota Flag]={'RTA'}, SaleYear={'2024'}>} [Sales]) -
Sum({<[Quota Flag]={'RTA'}, SaleYear={'2023'}>} [Sales])) /
Sum({<[Quota Flag]={'RTA'}, SaleYear={'2023'}>} [Sales])
) * 0.125

+

// RTA Sales Delta
Rank(
Sum({<[Quota Flag]={'RTA'}, SaleYear={'2024'}>} [Sales]) -
Sum({<[Quota Flag]={'RTA'}, SaleYear={'2023'}>} [ Sales])
) * 0.125

+

// Total Dollar Sales % Change
Rank(
(Sum({<[Quota Flag]={'TD'}, SaleYear={'2024'}>} [Sales]) -
Sum({<[Quota Flag]={'TD'}, SaleYear={'2023'}>} [ Sales])) /
Sum({<[Quota Flag]={'TD'}, SaleYear={'2023'}>} [Sales])
) * 0.25

+

// Total Dollar Sales Delta
Rank(
Sum({<[Quota Flag]={'TD'}, SaleYear={'2024'}>} [Sales]) -
Sum({<[Quota Flag]={'TD'}, SaleYear={'2023'}>} [Sales])
) * 0.25

+

// Contrast Agents Sales % Change
Rank(
(Sum({<[Quota Flag]={'CA'}, SaleYear={'2024'}>} [Sales]) -
Sum({<[Quota Flag]={'CA'}, SaleYear={'2023'}>} [Sales])) /
Sum({<[Quota Flag]={'CA'}, SaleYear={'2023'}>} [ Sales])
) * 0.125

+

// Contrast Agents Sales Delta
Rank(
Sum({<[Quota Flag]={'CA'}, SaleYear={'2024'}>} [Sales]) -
Sum({<[Quota Flag]={'CA'}, SaleYear={'2023'}>} [Sales])
) * 0.125
))

[diegozecchini]

Hi!
The issue with ranking duplications in Qlik's Rank() function often arises due to either identical calculated values or decimal precision issues.

If multiple records result in the same calculated value, Qlik will assign them the same rank. This behavior is by design.

You can use Dense or Fraction rank options in the Rank() function.
Rank(<expression>, 1)
This assigns sequential ranks without gaps.

If the calculated values differ only slightly (e.g., in the nth decimal place), rounding issues might make them appear identical to Qlik.

In this case use the Round() function to explicitly control the precision of each computed value:

Round(Sum({<[Quota Flag]={'RTA'}, SaleYear={'2024'}>} [Sales]), 0.001)

Then , since you're using nested Rank() functions in your formula, ensure that each part of your expression operates independently without unintended overlaps.

Wrap each computed Rank() block in a Floor() or Round() function to normalize values:

Rank(
Floor(
(Sum({<[Quota Flag]={'RTA'}, SaleYear={'2024'}>} [Sales]) -
Sum({<[Quota Flag]={'RTA'}, SaleYear={'2023'}>} [Sales])) /
Sum({<[Quota Flag]={'RTA'}, SaleYear={'2023'}>} [Sales]),
0.001
)

Or, you can use all these combinations together.

[Noor51]

I tried both floor and round function, but unsure that setup the set expression is right, bc they both improve but not solve the problem. I also tried putting ,1 in the end of the rank function.

Rank(Count(DISTINCT [Rep Name]) -
(
// RTA Sales % Change
Rank(
Floor(
(Sum({<[Quota Flag]={'RTA'}, SaleYear={'2024'}>} [ Sales]) -
Sum({<[Quota Flag]={'RTA'}, SaleYear={'2023'}>} [Sales])) /
Sum({<[Quota Flag]={'RTA'}, SaleYear={'2023'}>} [Sales]),0.001)
) * 0.125

+

// RTA Sales Delta
Rank(
Floor(
Sum({<[Quota Flag]={'RTA'}, SaleYear={'2024'}>} [Sales]) -
Sum({<[Quota Flag]={'RTA'}, SaleYear={'2023'}>} [Sales]),0.001)
) * 0.125

[diegozecchini]

After ensuring each calculation is rounded or normalized, the final Rank() function should be applied to the entire composite calculation.

Rank(
Count(DISTINCT [Rep Name]) -
(
Rank(Round((Sum({<[Quota Flag]={'RTA'}, SaleYear={'2024'}>} [Sales]) - Sum({<[Quota Flag]={'RTA'}, SaleYear={'2023'}>} [Sales])) / Sum({<[Quota Flag]={'RTA'}, SaleYear={'2023'}>} [Sales]), 0.001)) * 0.125 +
Rank(Round(Sum({<[Quota Flag]={'RTA'}, SaleYear={'2024'}>} [Sales]) - Sum({<[Quota Flag]={'RTA'}, SaleYear={'2023'}>} [Sales]), 0.001)) * 0.125 +
Rank(Round((Sum({<[Quota Flag]={'TD'}, SaleYear={'2024'}>} [Sales]) - Sum({<[Quota Flag]={'TD'}, SaleYear={'2023'}>} [Sales])) / Sum({<[Quota Flag]={'TD'}, SaleYear={'2023'}>} [Sales]), 0.001)) * 0.25 +
Rank(Round(Sum({<[Quota Flag]={'TD'}, SaleYear={'2024'}>} [Sales]) - Sum({<[Quota Flag]={'TD'}, SaleYear={'2023'}>} [Sales]), 0.001)) * 0.25 +
Rank(Round((Sum({<[Quota Flag]={'CA'}, SaleYear={'2024'}>} [Sales]) - Sum({<[Quota Flag]={'CA'}, SaleYear={'2023'}>} [Sales])) / Sum({<[Quota Flag]={'CA'}, SaleYear={'2023'}>} [Sales]), 0.001)) * 0.125 +
Rank(Round(Sum({<[Quota Flag]={'CA'}, SaleYear={'2024'}>} [Sales]) - Sum({<[Quota Flag]={'CA'}, SaleYear={'2023'}>} [Sales]), 0.001)) * 0.125
)
)