Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vishuraina
New Contributor II

Creating an aggregated dimension filter : Urgent requirement

Hi,

This is an urgent requirement. I've the data in the below format and it has data for 10 different brands in the similar manner as given below. I need to create an aggregated dimension out of this which would in turn be used as a filter. The requirement is to create 'Top 3 Brands', 'Top 5 Brands', 'Top 7 Brands','Top 10 Brands'  drop downs from the available data basis sales. So the aggregated dimension should act as a filter with the mentioned drop downs.

BrandsYearSales
a2017500
a2017400
b2017450
b2017399
b2016560
c2016457
c20171231
c2017323
5 Replies
kaanerisen
Contributor III

Re: Creating an aggregated dimension filter : Urgent requirement

Hi vishal,

Sample Script:


fact:

load * Inline [

Brands,Year,Sales

a,2017,500

a,2017,400

b,2017,450

b,2017,399

b,2016,560

c,2016,457

c,2017,1231

c,2017,323

];

TopDim:

load * Inline [

TopN,N

Top1,1

Top2,2

Top3,3

];

Dimension:

Brands

Year

Measure:

Sum({<Brands={"=aggr(rank(Sum(Sales)),Brands)<=Max(N)"}>}Sales)

Untitled.png

vishuraina
New Contributor II

Re: Creating an aggregated dimension filter : Urgent requirement

Hi Kaan,

Thanks for your help on this. I however want the created filter to act as a Global filter just like normal filters, on all the visualizations across all views. With this i'l be able to apply it on only selected tables/visualizations,

ogautier62
Valued Contributor II

Re: Creating an aggregated dimension filter : Urgent requirement

Hi,

may be create a column with the rank, and make it used for search,

and the search acts as a filter if you type :  <=2 or <=3 and so on

regards

vishuraina
New Contributor II

Re: Creating an aggregated dimension filter : Urgent requirement

I have created this Dimension :

If((Aggr(Rank(Sum(${<YEAR = {$(=Max(YEAR))}>}Sales)),BRAND)) < 6, 'Top 05',

If((Aggr(Rank(Sum({$<YEAR = {$(=Max(YEAR))}>}Sales)),BRAND)) > 11, 'Top 10',

If((Aggr(Rank(Sum({$<YEAR = {$(=Max(YEAR))}>}Sales)),BRAND)) > 16 , 'Top 15')))


But this gives results as Top 5, Top 5-10, Top 10-15. I know I'm close to the solution, just not getting the required thing.

ogautier62
Valued Contributor II

Re: Creating an aggregated dimension filter : Urgent requirement

Try this

In dim just : if(aggr(rank(-sum...... <=N, top)

In your script load table in-line field top, N

With top5, 5

Top10,10

Top15,15

I think you must have to click more in the sheet to select as filter

Community Browser