Qlik Community

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

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:

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)

Capture.PNG

4 Replies

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,

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:

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)

Capture.PNG

Not applicable

Re: Variable limit straight table

Thanks a lot

Community Browser