Sum all records with some field less than the given record

[arturomiquelveyrat]

A table has the following fields: PLAN, RANKING and ASSETS. We need to calculate SUPERIOR_ASSETS, which is the sum of the assets of the plans with a higher ranking:

PLAN	RANKING	ASSETS	SUPERIOR_ASSETS
Plan A	1	100	0
Plan B	2	100	100
Plan C	3	100	200
Plan D	4	100	300

The expression I've built so far is:

Aggr(
Sum(
{<
  RANKING = {"< X"}
>}
Total
ASSETS
),
PLAN
)

The question is how to substitute the "X" with a variable / expression that takes the value of the RANKING in each plan.

I've attached a QlikView and an Excel file to make testing solutions easier.

Note: the table object will not contain the RANKING, so functions like Column() or Top() will not be useful.

Thank you!

[sunny_talwar]

That makes much more sense now.... What do you want the output to look like when there is duplicate Ranking?

PLAN	RANKING	ASSETS	SUPERIOR_ASSETS
Plan A	1	100	0
Plan B	2	100	?
Plan C	2	100	?
Plan D	3	100	?

What should the output look like here?

[arturomiquelveyrat]

Great!

Since SUPERIOR_ASSETS is the sum of the ASSETS of plans with higher ranking than the given record, it should look like this:

PLAN	RANKING	ASSETS	SUPERIOR_ASSETS
Plan A	1	100	0
Plan B	2	100	100
Plan C	2	100	100
Plan D	3	100	300

Thanks!

[sunny_talwar]

Try this

Aggr(If(RANKING = Above(TOTAL RANKING), Above(TOTAL RangeSum(Above(TOTAL Sum(ASSETS), 1, RowNo(TOTAL)))), RangeSum(Above(TOTAL Sum(ASSETS), 1, RowNo(TOTAL)))),(RANKING,(NUMERIC)), PLAN)

[arturomiquelveyrat]

You're right, it works! Very nice job

Thanks!

[arturomiquelveyrat]

I've just realised that, if you select a record in the table, your solution doesn't work anymore since the result goes to 0.

Do you think it's possible to solve this? I guess that, as long as inter-register functions like Above() are used, the solution will fail when some selection is made on the table.

Thanks!

[arturomiquelveyrat · Accepted solution]

I've fixed it:

Just add {1} to the Sum()'s in your code to ensure all records are selected:

Aggr(

     If(RANKING = Above(TOTAL RANKING),

          Above(TOTAL RangeSum(Above(TOTAL Sum({1} ASSETS), 1, RowNo(TOTAL)))),

          RangeSum(Above(TOTAL Sum({1} ASSETS), 1, RowNo(TOTAL)))

          ),

     (RANKING,(NUMERIC)),

     PLAN

     )