Excel Product.Sum in set expression

[Noor51]

Hi everyone, 

I'm on qlik sense on prem with May 2023 update. 

I want to do a weight avg of a rank in qlik sense this is what I have so far in qlik sense set expression but it isn't work as need. Please help. 

Rank(((Sum({<[Flag]={'RTA'}, SaleYear={'2024'}>} [Sales]) - 

Sum({<[Flag]={'RTA'}, SaleYear={'2023'}>} [Sales])) / 

Sum({<[Flag]={'RTA'}, SaleYear={'2023'}>} [Sales]) * 0.25)

+

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

Sum({<[Flag]={'RTA'}, SaleYear={'2023'}>} [Sales])) * 0.25)

+

Rank(((Sum({<[Flag]={'TD'}, SaleYear={'2024'}>} [Sales]) - 

Sum({<[Flag]={'TD'}, SaleYear={'2023'}>} [Sales])) / 

Sum({<[Flag]={'TD'}, SaleYear={'2023'}>} [Sales]) * 0.125)

+

((Sum({<[Flag]={'TD'}, SaleYear={'2024'}>} [Sales]) - 

Sum({<[Flag]={'TD'}, SaleYear={'2023'}>} [Sales])) * 0.125)

+ 

Rank(((Sum({<[Flag]={'CA'}, SaleYear={'2024'}>} [Sales]) - 

Sum({<[Flag]={'CA'}, SaleYear={'2023'}>} [Sales])) / 

Sum({<[Flag]={'CA'}, SaleYear={'2023'}>} [Sales]) * 0.125)

+

((Sum({<[Flag]={'CA'}, SaleYear={'2024'}>} [Sales]) - 

Sum({<[Flag]={'CA'}, SaleYear={'2023'}>} [Sales])) * 0.125)

[marcus_sommer]

For me it looked like there are several logically issues - and in regard to the object-dimensionality there may further dependencies.

I suggest you split the task into several steps within a table chart - calculating each year and the absolute + relative differences as well as their rank within separate expressions. If they all be working like expected you could merge them again.

[Noor51]

I have try that. I have them in separate columns in a table and it work, but when I combine it doesn't work. Here is the code when I combine all the separate columns together 

// Weighted Average Rank
(

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

+

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

+

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

+

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

+

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

+

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

)

[marcus_sommer]

You have really 6 weighted rank-columns side by side in the object and then their addition failed?

[Noor51]

Yes. This is how it looks with the current weight average code. I don't understand why it isn't working honestly. 

[marcus_sommer · Accepted solution]

I would expect doubles as results by multiplying a rank with a rate-value and not integer.

[Noor51]

Do you know how to make the rank in reverse order. so that the highest number is rank 5 as example. 

[marcus_sommer · Accepted solution]

Rank() itself couldnt be reversed but depending on the scenario the expression result might be multiplied with -1 or substracted against a larger value. Another approach may be to count() the (total) dimension values and substracting from it the rank().