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

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

jsobrinho — Sat, 16 Nov 2024 01:07:24 GMT

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.

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

rwunderlich — Fri, 06 Mar 2020 02:41:24 GMT

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

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

jsobrinho — Fri, 06 Mar 2020 11:48:59 GMT

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)

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)

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

jsobrinho — Mon, 09 Mar 2020 17:28:19 GMT

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)