How to identify how many rows have value above current row value

[Loh Norbert]

Hi I need to find a way to calculate how many stores have more fruits based on the fruit in data canvas using expressions. I tried using rowid and over but the values are never correct.

Sample Dataset

 

 

 

 

StoreID

Quantity

Fruit

 

 

1

2

Apple

 

 

2

4

Apple

 

 

3

5

Pear

 

 

4

6

Apple

 

 

5

7

Orange

 

 

 

 

 

Ideal Result

 

 

 

 

StoreID

Quantity

Fruit

Number of Stores with more quantity

StoreID with more Quantity

 

 

1

2

Apple

2

2, 4

 

 

2

4

Apple

1

4

 

 

3

5

Pear

0

(empty)

 

 

4

6

Apple

0

(empty)

 

 

5

7

Orange

0

(empty)

[Fabian Duerr]

You can do this with a data function that takes your data table as input and returns two new columns or a new data table. Are you familiar with data functions

With your data function loop thru every row and filter your input table to the fruit (=) and the quantity (>) of your current row. The remaining StoreIDs (column/vector) will give you the last column of your requested output and the length of this vector is the number of stores with more quantities.

Let me know if you need further help.

[Loh Norbert]

I had a similar idea but have only tried doing it using the built in expressions. Im not very familar with data functions but I shall try. Thank you