topic Re: Get value form rank in App Development

Get value form rank

NataliaKuz — Sat, 16 Nov 2024 17:42:53 GMT

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!

Re: Get value form rank

Oleg_Troyansky — Fri, 11 Dec 2020 16:06:36 GMT

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,

Re: Get value form rank

NataliaKuz — Sun, 13 Dec 2020 15:03:16 GMT

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%.

Re: Get value form rank

Kushal_Chawda — Sun, 13 Dec 2020 15:43:02 GMT

@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