Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
rajinikanth
New Contributor

Top N based multiple dimensions

I have data as below...

countryregionsales
indiaasia500
chinaasia200
japanasia100
pakistanmena600
frnacemena300
usaEur1000

 

My requirement is, i need a list box, where i can select top sales dynamically.

And another list box which defines the dimension on which it has to be applied.  as below

Capture.JPG

when i select top 2 and by country,  the output should be usa and pakistan 

when i select top 2 and by region,  the output should be mena and eur 

output in straight table

 

 

2 Replies
YoussefBelloum
Esteemed Contributor

Re: Top N based multiple dimensions

Hi,

You can proceed like this:

1) create a dissociated table with one field "rank"

top:
Load * Inline [
rank
1
2
3
4
5
]; 

2) create a variable vRank to get the value of the field rank

vRank=Only(rank) on the variables panel

3) on your table, use expression like this:

sum({<country={"=Rank(sum(sales))<=$(vRank)"}>}sales)

4) choose your rank via a listbox using the field rank, and the country and regions values using your fields described above

Mauritz_SA
Contributor III

Re: Top N based multiple dimensions

Hi there

You can do the following:

Using Variable input extension in standard Qlik Dashboard Bundle:

1. Create two variables, vDimension and vTopX.

2. Create a variable input sheet object using Custom Objects -> Qlik Dashboard Bundle -> Variable Input. Link it to the vDimension variable, give it fixed values. Set up two separate values, [Country] and [Region].

3. Create a variable input sheet object using Custom Objects -> Qlik Dashboard Bundle -> Variable Input. Link it to the vTopX variable, give it fixed values. Set up three separate values 2, 3 and 4.

4. Create a table and put in a measure =Sum(sales).

5. Make the dimension =$(vDimension).

6. Put a Fixed Number limitation on the Dimension and in the expression editor, put $(vTopX).

If everything works fine then you should be able to change the table's output using the two variable input extensions.

Benefit: No random tables in data model viewer and no permanent selections in selections bar.

Not using extensions:

1. Create two island tables. One with a column named TopX and values of 2, 3 and 4. Another one with a column DimensionX and values country and region.

2. Create two variables, vDimension and vTopX.

3. Make vDimension's definition =[DimensionX] and make vTopX's definition =[TopX].

4. Create a table and put in a measure =Sum(sales).

5. Make the dimension =$(vDimension).

6. Put a Fixed Number limitation on the Dimension and in the expression editor, put $(vTopX).

7. If you want to, make the TopX and DimensionX fields Always one selected value.

Benefit: Can be bookmarked and doesn't use extensions.

Good luck!

Mauritz