Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
124psu
Contributor

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