Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
newqlik2017
Creator II
Creator II

How to get TOTAL of a Ranked Column

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?

Ranking_Test1.JPG

1 Solution

Accepted Solutions
sunny_talwar

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))


Capture.PNG

View solution in original post

4 Replies
sunny_talwar

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))


Capture.PNG

newqlik2017
Creator II
Creator II
Author

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.

sunny_talwar

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))

newqlik2017
Creator II
Creator II
Author

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