Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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?
No dimension, two expressions:
= Sum({<Shop = {'Shop A'}>}Value) / Sum({1} Value)
= 1- Sum({<Shop = {'Shop A'}>}Value) / Sum({1} Value)
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
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.
What I did is the following:
* I add the pie chart, it asks me to add the measure and its dimension
* 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
Can you help me? Please
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)
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?
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)