Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

How to create 'others' considering that the are values < 10%

HI guys,

I have a table that contains the Client name and sales values. I need to show in the column Client, 'Others' when the market share of this Client is minus that 10%.

this is an example (by Product) because I have more hierarchies as by country, by state, by city.... So for each hierarchy, I need to divide the sales to a market share different. By country, I have a total,
by state I have another total to divide.

Other.png

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Creator
Creator

Hello,

My problem was solved, using the variable to determine the context: Country or Product

=aggr(
if(
sum(Sales)/$(variabel)< 0.10
,'Others',Client),
Client
,CountryFilter
,ProductFilter)

View solution in original post

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

For the Client Dimension in the chart, use a calculated dimension like this:

=Aggr(if(Sum([Mkt Share]) < 0.1, 'Others', Client), Client)

For any other dim like State, substitute State for Client.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Highlighted
Creator
Creator

First of all, thank you for your answer.

I think that your answer solved one of my problems 🙂

I attached the qvw file with the scenario : Country:  USA and Filter like ALL, I have two tables:

-by Country (The result its ok with USA or ALL) 

-by Product (When I had select the ALL product I hope that the result is equal to by Country table)

Other and ALL.png

 I have ALL to considering ALL products, so when I select a product ALL I hope that the market share be considering the units per country, client and product (where product ALL has products values in my example 174)

The current result is that, although I am having to select ALL the market share is grouped by product and not by ALL

 

One solution is to create a variable, and change my calculated dimension in my the Product table? 

if(ProductFilter='ALL',Total_Country,Total_Product) 

=if(
aggr(
sum(Sales)
,Client
,CountryFilter
,ProductFilter
)
/$(variabel)< 0.10

,'Others',Client)

Highlighted
Creator
Creator

Hello,

My problem was solved, using the variable to determine the context: Country or Product

=aggr(
if(
sum(Sales)/$(variabel)< 0.10
,'Others',Client),
Client
,CountryFilter
,ProductFilter)

View solution in original post