# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

## 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

1 Solution

Accepted Solutions
MVP

## 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)

4 Replies
MVP

## Re: Variable limit straight table

What is the expected output here?

Not applicable

## 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,

MVP

## 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)

Not applicable

Thanks a lot