topic Re: Variable limit straight table in QlikView

Variable limit straight table

Wed, 15 Jun 2016 13:30:53 GMT

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 !

Re: Variable limit straight table

sunny_talwar — Wed, 15 Jun 2016 13:48:32 GMT

What is the expected output here?

Re: Variable limit straight table

Wed, 15 Jun 2016 13:54:37 GMT

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

sunny_talwar — Wed, 15 Jun 2016 15:22:37 GMT

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)

Re: Variable limit straight table

Wed, 15 Jun 2016 15:56:00 GMT

Thanks a lot