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]

How is Ranking determined if it is not given?

[arturomiquelveyrat]

Hi Sunny,

The field RANKING is in one of the tables of the data model, but will not appear in the table object.

[sunny_talwar]

But you said it won't be available in the dashboard, how are we suppose to know what the ranking is, if it is not given? Is there a way to derive this using other available fields?

[effinty2112]

Hi Arturo,

Maybe:

PLAN	Rangesum(above(TOTAL Sum(ASSETS),1,RowNo(TOTAL)))
Plan A	0
Plan B	100
Plan C	200
Plan D	300

Regards

Andrew

[arturomiquelveyrat]

Hi Andrew,

Thanks, but this only works if the  table is ordered by RANKING.

[effinty2112]

Hi Arturo,

try:

PLAN	Aggr(Rangesum(above(TOTAL Sum(ASSETS),1,RowNo(TOTAL))),(RANKING,NUMERIC,ASCENDING))
Plan D	300
Plan C	200
Plan B	100
Plan A	0

PLAN	Aggr(Rangesum(above(TOTAL Sum(ASSETS),1,RowNo(TOTAL))),(RANKING,NUMERIC,ASCENDING))
Plan C	200
Plan A	0
Plan D	300
Plan B	100

Cheers

Andrew

[arturomiquelveyrat]

Thanks Andrew! This is very helpful.

However, I guess this will fail if 2 records happen to have the same RANKING.

[sunny_talwar]

I am just not sure how this is going to work without the RANKING field being available? Are you saying that RANKING is now available?

[arturomiquelveyrat]

Hi Sunny,

It seems I didn't make myself clear enough. The field RANKING is available in the dashboard. All I've just wanted to state is that RANKING is not going to be in the table object I'm trying to create.