15 Replies Latest reply: Apr 5, 2018 3:25 AM by Stefan Wühl

# Market share

Good day to everyone.

I am in a BI project, in this project I have to show the participation of a business in front of its competition, both belonging to the same line.

Example

"In an urban area there are 5 record stores:

SHOP A,

SHOP B,

SHOP C,

SHOP D

SHOP E,

all these stores represent 100%, but I need to know the percentage of the SHOP A compared to the other stores (these have to be grouped as "Market") so that this way you can read something like this: SHOP A has a participation of 19% against its market which is 81%.

I would greatly appreciate your help.

Thanks

• ###### Re: Market share

You could calculate the selected shop's share like

= Sum(Value) / Sum({1} TOTAL Value)

and the market share would be

=1 - Sum(Value) / Sum({1} TOTAL Value)

• ###### Re: Market share

But ... if I want to show this example in a pie chart, where SHOP A is shown and the rest as "Market"

How could it be?

• ###### Re: Market share

No dimension, two expressions:

= Sum({<Shop = {'Shop A'}>}Value) / Sum({1} Value)

= 1- Sum({<Shop = {'Shop A'}>}Value) / Sum({1} Value)

• ###### Re: Market share

I am sorry

I do not want to be so annoying but ... I still do not understand how the example of the graph would look, I tried to add the two expressions but I get an error, so I do not know what measure add.

It is clear that I am new to Qlik Sense therefore there are things that I still do not know

• ###### Re: Market share

What error do you get?

And how does your model look like? Of course you need to adapt the above expressions to your field names of your model.

• ###### Re: Market share

What I did is the following:

* In dimension I add the amount (the sum of this)

* In measure, I open the expression editor and add the ones you gave me, obviously appropriate to the fields that I have, being as follows:

= Sum ({<FAM_N_GIRO = {'GAS STATIONS'}>} AMOUNT) / Sum ({1} AMOUNT)

= 1- Sum ({<FAM_N_GIRO = {'GAS STATIONS'}>} AMOUNT) / Sum ({1} AMOUNT)

but it tells me that the result is zero or null

• ###### Re: Market share

In a sense pie chart, try a calculated dimension like

=If(FAM_N_GIRO = 'GAS STATIONS', FAM_N_GIRO, 'Market')

and as single expression

= Sum({1} AMOUNT)

• ###### Re: Market share

Very thanks!

It worked perfectly

Only two questions more:

First

Is there any way to get the value of a selected field? So that from there the expression looks like this:

= If (FAM_N_GIRO = 'SelectedField', FAM_N_GIRO, 'Market')

Depending on what you select and what else you take as "Market"

Second

In the expression:

= Sum ({1} AMOUNT)

What does {1} mean?

• ###### Re: Market share

1) maybe like

=If(Match(FAM_N_GIRO , \$(=Concat(DISTINCT chr(39)&FAM_N_GIRO &chr(39),','))), FAM_N_GIRO ,'Market')

2) The set identifier {1} is basically telling to ignore all selections, i.e. consider all data in your model.

Hence Market will always contain all other data.

If you need to ignore only Region, but consider e.g. Products or Dates, you can also only ignore Region field selections, like

=Sum({<FAM_N_GIRO = >} AMOUNT)

• ###### Re: Market share

Thank you very very much!

You have helped me a lot, you just would mind explaining the next part of the expression:

\$ (= Concat (DISTINCT chr (39) & FAM_N_GIRO & chr (39), ','))

• ###### Re: Market share

That's a dollar sign expansion.

It will just input a list of possible FAM_N_GIRO values, comma separated and quoted in the calculated dimension, before the expression gets parsed and evaluated, like a preprocessor text replacement.

The Magic of Dollar Expansions

• ###### Re: Market share

Thaks!

• ###### Re: Market share

Good day!

The answer you gave me was correct.

I made a bar graph and on measure put the following function:

= Sum ({<FAM_N_GIRO =>} AMOUNT)

And I see two bars: one for "Market" and the other for the family that I select, but in the "amount" for "market" I omit the amount of the selected family.

My question is: How can I do so that in "Market" I see the total of all families and apart from the family selected?

• ###### Re: Market share

Not quite sure that I understand your last sentence.You want to see in Market the total value including the amount of the selected family?

With QlikView, it would be just a bar chart without a dimension and two expressions,

=Sum(AMOUNT)

=Sum ({<FAM_N_GIRO =>} AMOUNT)

two compare the selected AMOUNT against the total.

QlikSense does not allow a bar chart without a dimension, but you can create a synthetic, calculated dimension using ValueList():

=Valuelist('\$(=Only(FAM_N_GIRO))','Market')

and a single expression:

=If(Valuelist('\$(=Only(FAM_N_GIRO))','Market')='\$(=Only(FAM_N_GIRO))',

Sum(AMOUNT),

Sum({<FAM_N_GIRO>} AMOUNT)

)