Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
124psu
New Contributor III

syntax check for creating top 5 and bottom 5 tables on a dashboard

Hi All, so 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 (3)
2 Replies
Channa
Valued Contributor III

Re: syntax check for creating top 5 and bottom 5 tables on a dashboard

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

 

try this

Channa
124psu
New Contributor III

Re: syntax check for creating top 5 and bottom 5 tables on a dashboard

Hi Chana, sorry for the late reply. I have tried your method by replacing the first logic and also doing one stand alone but this still does not yield any results for me.