Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
124psu
Creator II
Creator II

return bottom 5 rows on a table using variables and button

Hi All, I posted this on a different forum but I believe this is the appropriate place for this question. I have 2 tables that are top and bottom 5 revenues for a specific region. I have 4 regions and they are tied to a button through the usage of a variable. I've got the top 5 working fine for all 4 regions. But when I modify my syntax to get the bottom 5 row values only one of the regions do not yield any data while the other 3 are working fine (literally just putting a '-' negative sign in front of my syntax to get the bottom 5 rows. I am lost at this point and really would like some feedback. 

BOTTOM 5 syntax - 

Aggr(
if($(vBot5) = 1,
If(Rank(TOTAL -Sum({<Region = {'USA'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue])) < 6, Sum({<Region = {'USA'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue])),

if($(vBot5) = 2,
If(Rank(TOTAL -Sum({<Region = {'China'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue])) < 6, Sum({<Region = {'China'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue])),

if($(vBot5) = 3,
If(Rank(TOTAL -Sum({<Region = {'Brazil'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue])) < 6, Sum({<Region = {'Brazil'}, [Status] = {'Ready', 'Partial Ready'}>}[Revenue])),

if($(vBot5) = 4,
If(Rank(TOTAL -Sum({<Region = {'Canada'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue])) < 6, Sum({<Region = {'Canada'}, [Status] = {'Ready', 'Partial Ready'}>} [Revenue]))

)
)
)
)
, [Account], [Account_Name])

As mentioned, for the regions China, Brazil, Canada my code returns 5 rows of negative numbers for revenue which is absolutely correct. But for some reason, USA (my first button) does not yield any results when in fact it should.

Labels (2)
0 Replies