topic How to get TOTAL of a Ranked Column in QlikView

How to get TOTAL of a Ranked Column

newqlik2017 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I've a STATE table as listed below. Now, I need a total row that displays a state with the highest amount (sum of all the amounts for that state irrespective of the UNIT). Any ideas?

Re: How to get TOTAL of a Ranked Column

sunny_talwar — Thu, 06 Sep 2018 14:49:37 GMT

Not the best way to do this, but can't think of a better way

STATE

If(Dimensionality() = 0,

Pick(Aggr(Rank(Sum({< DATE = {'201806'}>} AMOUNT)), UNIT, STATE),

FirstSortedValue(DISTINCT STATE, -Aggr(Sum(TOTAL <STATE> {< DATE = {'201806'}>} If(Aggr(Rank(Sum({< DATE = {'201806'}>} AMOUNT)), UNIT, STATE) = 1, AMOUNT)), UNIT, STATE)),

FirstSortedValue(DISTINCT STATE, -Aggr(Sum(TOTAL <STATE> {< DATE = {'201806'}>} If(Aggr(Rank(Sum({< DATE = {'201806'}>} AMOUNT)), UNIT, STATE) = 2, AMOUNT)), UNIT, STATE))),

AGGR(IF(RANK(SUM({< DATE = {'201806'} >} AMOUNT)) <= 2 , STATE, 'All Other'), UNIT, STATE))

AMT

If(Dimensionality() = 0,

Pick(Aggr(Rank(Sum({< DATE = {'201806'}>} AMOUNT)), UNIT, STATE),

Max(Aggr(Sum(TOTAL <STATE> {< DATE = {'201806'}>} If(Aggr(Rank(Sum({< DATE = {'201806'}>} AMOUNT)), UNIT, STATE) = 1, AMOUNT)), UNIT, STATE)),

Max(Aggr(Sum(TOTAL <STATE> {< DATE = {'201806'}>} If(Aggr(Rank(Sum({< DATE = {'201806'}>} AMOUNT)), UNIT, STATE) = 2, AMOUNT)), UNIT, STATE))),

Sum({<DATE = {'201806'}>} AMOUNT) + Sum({1}0))

Re: How to get TOTAL of a Ranked Column

newqlik2017 — Thu, 06 Sep 2018 18:02:03 GMT

Hi Sunny, this works although there might be alternate ways.

Question: If I need to the Total for TX and FL and display in a separate text box - how can we do that?

For instance, TX = 15,000 & FL = 9,000. Requirement is to display this as "Other" - 24,000. So I was thinking to use a text box for the sum.

Re: How to get TOTAL of a Ranked Column

sunny_talwar — Thu, 06 Sep 2018 18:06:57 GMT

For Rank 1

=Max(Aggr(Sum(TOTAL <STATE> {< DATE = {'201806'}>} If(Aggr(Rank(Sum({< DATE = {'201806'}>} AMOUNT)), UNIT, STATE) = 1, AMOUNT)), UNIT, STATE))

For Rank 2

=Max(Aggr(Sum(TOTAL <STATE> {< DATE = {'201806'}>} If(Aggr(Rank(Sum({< DATE = {'201806'}>} AMOUNT)), UNIT, STATE) = 2, AMOUNT)), UNIT, STATE))

Re: How to get TOTAL of a Ranked Column

newqlik2017 — Thu, 06 Sep 2018 18:17:28 GMT

Ahh, got it. You are awesome. Thank you very much!!!