Top N based multiple dimensions

[rajinikanth]

I have data as below...

country	region	sales
india	asia	500
china	asia	200
japan	asia	100
pakistan	mena	600
frnace	mena	300
usa	Eur	1000

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

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

[YoussefBelloum]

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]

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