4 Replies Latest reply: Jun 15, 2016 11:56 AM by Alexandre Charpentier

# Variable limit straight table

Hi,

I please find attached the file.

I want to calculate the sales in a straight table where the dimension is the fiscal year.

The issue is that I want to limit the sum of the sales with the ranks of the customers.

And this limit changes with the fiscal year. It's not a fixed number.

Thank you !

JP

• ###### Re: Variable limit straight table

What is the expected output here?

• ###### Re: Variable limit straight table

Hi Sunny,

.

See the file, the current formula doesn't work. It should show: 2400, 3000,3500.

The story is that I want to summarize the revenue made by the top 1% (in volume) of my customers. And each fiscal year this number changes.

Best regards,

• ###### Re: Variable limit straight table

You can try this:

=Sum(Aggr(If(Rank <= RankLimit,  Sales), FY, Rank))

Or you can do this with little script manipulation

Limit:

FY, RankLimit

1, 3

2, 4

3, 5

];

Data:

AutoNumber(FY&Rank) as Key;

FY, Customer, Rank, Sales

1, A, 1, 900

1, B, 2, 800

1, C, 3, 700

1, D, 4, 600

1, E, 5, 500

1, F, 6, 400

1, G, 7, 300

1, H, 8, 200

2, A, 2, 800

2, B, 3, 700

2, C, 1, 900

2, D, 4, 600

2, E, 5, 500

2, F, 6, 400

2, G, 7, 300

2, H, 8, 200

3, A, 1, 900

3, B, 8, 200

3, C, 2, 800

3, D, 3, 700

3, E, 4, 600

3, F, 5, 500

3, G, 6, 400

3, H, 7, 300

];

And the expression:=Sum({<Key = {"=Rank <= RankLimit"}>} Sales)