Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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?
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!
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)
You're right, it works! Very nice job
Thanks!
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!
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
)