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!
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
)
How is Ranking determined if it is not given?
Hi Sunny,
The field RANKING is in one of the tables of the data model, but will not appear in the table object.
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?
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
Hi Andrew,
Thanks, but this only works if the table is ordered by RANKING.
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
Thanks Andrew! This is very helpful.
However, I guess this will fail if 2 records happen to have the same RANKING.
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?
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.