Get value form rank

[NataliaKuz]

Hello, Qlik Sense people, you are my last hope. I have table with products, priorities and calculated distribution (% of stores selling this product). Assuming that "the higher priority, the bigger should be distribution", I need the following (RED COLUMN):

Priority	Product	Distribution [Expression]	Rank(DistrExpression)	Potential Distr
1	A	100%	1	100%
2	B	70%	3	80%
3	C	60%	4	70%
4	D	80%	2	60%
5	E	50%	5	50%

I need to get value with Rank(DistrExpression) from 3 rd column, ignoring product stated in each line. Is it possible?

All your suggestions will be very helpfull,

Thank you!

[Oleg_Troyansky · Accepted solution]

Hi Natalia,

Yes, you can do it, and it's rather simple. You can refer to chart measures by number or by name. So, one othe following should work:

Column(2) - for the second measure in the chart, or

[Rank(DistrExpression)] - assuming that this is the label of the Measure.

The label may not be recognized by the Expression Editor, but if you ignore the error message and approve the change, it should produce the desired result.

Cheers,

[NataliaKuz]

Dear Oleg, Thank you for fast reply. I can set all the columns except the last one (colored with red). My problem is that I can't retrieve distribution value based on SKU rank (I don`t know function name for it).

In my example product B has the third highest Distribution from the whole range, but we treat it as 2nd priority. Thus it should has higher Distribution - exact potential level is 80%.

[Kushal_Chawda]

@NataliaKuz  try below expression.

Assuming that  Priority is in sequential number format. Also Priority and Product has one to one relationship.

=subfield(concat(total aggr(sum(Expr),Product),'|',aggr(rank(sum(Expr)),Product)),'|',Priority)

Note:  Replace sum(Expr) with your  DistrExpression