topic Re: Market Share Calculation using Set Analysis in QlikView

Market Share Calculation using Set Analysis

Fri, 20 Nov 2020 18:02:30 GMT

Hello All,

I am trying to calculate sum(sales) of current brand selection and divide it by sum(Sales) for the associated market.

Not sure how to calculate overall sales for a specific market as only Brand is selected( market is not selected) .

i am trying to calculate it as sum(Sales)/ sum(total<Market>sales) where denominator gives me total market value whereas i need to calculate it only for associated market value for the brand selected

Re: Market Share Calculation using Set Analysis

agomes1971 — Thu, 29 Jan 2015 14:17:08 GMT

Hi,

please see attached

HTH

André Gomes

Re: Market Share Calculation using Set Analysis

Thu, 29 Jan 2015 14:18:42 GMT

Sorry, but i use the personal edition

Re: Market Share Calculation using Set Analysis

agomes1971 — Thu, 29 Jan 2015 14:43:56 GMT

Hi,

so how do you want me to help you?

Regards

André Gomes

Re: Market Share Calculation using Set Analysis

rubenmarin — Thu, 29 Jan 2015 14:44:24 GMT

Hi, can you try if this expression can work for you?:

sum(Value)/sum(total<Market> {<Brand>} Value)

This can work on a table with Brand as dimension, so that dimension filters the market.

Yyu can test in the straight table, for a row with Brand it will be correct, but the total row will sum all the brands in all the the markets, this is because there is no brand filtering.

Re: Market Share Calculation using Set Analysis

Fri, 30 Jan 2015 06:19:19 GMT

Hello Ruben,

the expression doesnt work. on selection of year , month and Brand (IN01). i want to calculate sum(sales) for IN01 / sum(sales) for Indigo Market only.

your expression gives me the value for all markets. I want to calculate for the associated market for IN01 only

Re: Market Share Calculation using Set Analysis

Fri, 30 Jan 2015 06:21:19 GMT

Hi Andre,

I have tried several expressions, but couldnt get through. Just need a suggestion on how do i write the expression for associated value which isnt selected? I want to select only Brand and not market. Just the market associated to the brand should be calculated as sum(sales) for that market.

Re: Market Share Calculation using Set Analysis

Fri, 30 Jan 2015 07:07:28 GMT

Hi Adity,

Try this expression :

sum(TOTAL {$<Market = P({1<Brand={'$(=GetFieldSelections(Brand))'}>}),Brand>} Value )

thanks

Sovan

Re: Market Share Calculation using Set Analysis

Gysbert_Wassenaar — Fri, 30 Jan 2015 10:01:05 GMT

Try: sum(Value) / sum({<Brand=,Market=p(Market)>} Value)

Re: Market Share Calculation using Set Analysis

Fri, 30 Jan 2015 12:59:47 GMT

Thanks a lot. It worked. could you please explain me the expression.

Re: Market Share Calculation using Set Analysis

Fri, 30 Jan 2015 13:00:27 GMT

Thanks Sovan. Could you please help me understand the expression.

Re: Market Share Calculation using Set Analysis

Gysbert_Wassenaar — Fri, 30 Jan 2015 13:21:27 GMT

{<Brand=,Market=p(Market)>} selects only those records where the market is the same as the market of the selected brands.

Market = p(Market) will include only the market(s) of the selected brand(s). In other words the Possible values of Market given the selections you made.

Brand= will make sure that Brand will not be filtered so all brands of the market of the selected market will be included.

Re: Market Share Calculation using Set Analysis

Fri, 30 Jan 2015 13:51:23 GMT

Thanks a lot. but i was wondering, since brand is a filter we are selecting, cant we write <brand={"$(=Brand)"}> so that it takes the associated market value for the only brand we select?

Re: Market Share Calculation using Set Analysis

Gysbert_Wassenaar — Fri, 30 Jan 2015 13:57:41 GMT

No, because you want all the brands with the same market. If you fix the value of brand to the selected brand you get ... only the selected brand.

Re: Market Share Calculation using Set Analysis

Wed, 04 Feb 2015 11:40:03 GMT

Hello Gysbert,

If I include one more dimension'Country', is it possible to calculate Market Value in that case?

Brand	Country	Brand Value	Market Value
WC01	China	400	1200
WC01	India	200	1000
WC01	Germany	300	1400

Is the expression : sum({<Brand=,Market=p(Market), Country=p(Country)>} Value) correct?

Re: Market Share Calculation using Set Analysis

Gysbert_Wassenaar — Wed, 04 Feb 2015 17:53:14 GMT

What are you trying to calculate? The market share over all countries or the market share per country?

BTW, you could first simply test an expression before asking if it is correct. That is part of your job as a qlikview developer. You are ultimately the person responsible for making sure that expressions return correct results so that end users can rely on the information in the qlikview documents they use.

Re: Market Share Calculation using Set Analysis

Thu, 05 Feb 2015 09:23:24 GMT

Hello I m trying something complex , like calculating market share (brand value and market value) at country level

your expression gives me market value same for all countries, i want to break it at country level

Market	Brand	Country	Value
Wildcraft	WC01	China	400	1200
Wildcraft	WC01	China	200	1000	Brand value	wc01	900
Wildcraft	WC01	Germany	300	1400	Market value	wildcraft	1300
Wildcraft	WC02	China	200	3000
Wildcraft	WC02	India	200	3000	At Country level
					Brand Value	china	600
						germany	300
					Market value
						china	800
						germany	300

On selection of only Brand, To calculate the marketvalue countrywise, i wrote expression

sum({<brand=,MARKET=p(MARKET),COUNTRY=p(COUNTRY)>} VALUES)

which gave me incorrect values 1900 for all countries

			EXPECTED
Brand	Country	Market Value countrywise
WC01	China	1900	800
WC01	China	1900	800
WC01	Germany	1900	300

Re: Market Share Calculation using Set Analysis

Gysbert_Wassenaar — Thu, 05 Feb 2015 12:38:37 GMT

Perhaps attached qvw helps.