Variable limit straight table

[#1]

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

[sunny_talwar]

What is the expected output here?

[#3]

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,

[sunny_talwar · Accepted solution]

You can try this:

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

Or you can do this with little script manipulation

Limit:

LOAD * INLINE [

    FY, RankLimit

    1, 3

    2, 4

    3, 5

];

Data:

LOAD *,

  AutoNumber(FY&Rank) as Key;

LOAD * INLINE [

    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)

[#5]

Thanks a lot