Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Merge cells or Similary Merge cells

Hello to all,


I am a new user of Qliksense and I have a problem which I cannot solve.

I want to reproduce this pivot table :

Country

ProductMarket ShareWorld market share
GermanyBA1,6%0,3%

Germany

CA1,8%
GermanyCC2%

But i can't merge cells. Do you have any idea to help me ?

If we can't merge cells, how i can have a similary display (same value in each line) ?

Thank you in advance for any possible ideas.

By wishing you a pleasant day.

Best regards,

SD

14 Replies
OmarBenSalem

Hi again Salim,

Here we go:

The trick is to activate Total and then use dimensionalty().

What is dimensionality?

Suppose we have 3 dimensions, the first 1 is dimensionality()=1, second dimensionality()=2 and so on

As for the total: It's dimensionality()=0

So, what we do is:

1) activate the total in our dimension now (country &' :'&product) , the & is to concatenate different fields, expressions, strings..

Capture.PNG

2)alter our expression:

from :if(Aggr( sum([Market Share]),product)<2,sum([Market Share]),null())

to :

if(Dimensionality()=0,Avg({1}  total [Market Share]),

if(Aggr( sum([Market Share]),product)<2,sum([Market Share]),null()))

The {1} means give me the Avg market share no matter the selections we make.

total means it's independant of the dimension, it won't be divided by the dimension (no aggregation will be made, it will always show the same thing no matter the dimension it's divided by..)

The expressions means, if dimensionality()=0 (total), then give me the avg fort the all market share, else, give my expression.

Result:

Capture.PNG

See the attached app

Hope that answers your question?

Not applicable
Author

Thank you for your answer, but here your merge cells of column but i want merge of row of one column or put the same value in each row of this column.

I allow to ask you a second question.

I have this pivot table :

Capture2.PNG

, but I want calculated ratio in each line a ratio like this :

For example to BA  I want the value of the following calculation 51,.../43577,..

Do you have a idea ?

OmarBenSalem

Like this?

sum([Market Share])/ sum(total [Market Share])

Capture.PNG

Please mark helpful the answers that have been helpful to you (under action, buttom left)

Not applicable
Author

Thank you, i thought that I have use this formula.

So, do you think we can merge cells of one colum like my picture of my first message ? If we can't, do you think that we can put the same value in each row of this column ?

Thank you very much !

OmarBenSalem

What's the point of repeating the same value over and over again while you can have the information only once with other informations in the same time?

You simply add another measure:

avg({1} total Market)

Capture.PNG

See the attached app:

And don't forget to mark the correct answer as so if your questions have been answered !

Hope this helps?

Not applicable
Author

Yes i will mark the correct answer when i obtain the correct answer.

In first i want merge but if i can't i want the same value in each row for one country.

Indeed, the first measure show th market share of one product in the country. The second measure is the ratio of all market by population of country (it's why for one country it's the same value).

Then, when i use this expression "sum({<nom_Mesure={exp}>} total [valeur])", if I don't select a country (equals nom_Du_Pays) i don't have the good value :

if don't select value of country (equals nom_Du_Pays)

value.PNG

if I select Allemagne value

value2.PNG

Do you have an explain ?

I'm sorry for this questions...

OmarBenSalem

Good morning Salim, And hey, you don't have to be sorry for these questions ! You're not bothering me at all !

Be my guest

You can always add more measures in a table.

In your case, you can have 2 measures.

See the example below:

I want have a measure that returns the Avg market share by country product(so it depends on the dimensions)

and a measure that always shows the Total Avg market share:

So

we choose a table:

As dimensions:

Country

Product

As measures:

1) Avg([Market Share])

2) Avg( TOTAL [Market Share])

Result:

Capture.PNG

When I select a country, I will always have good results:

Capture.PNG

Capture.PNG

Not applicable
Author

For me, to obtain the Total for one country i need to write sum({<nom_Mesure={exp}>} total <nom_Du_Pays> [valeur]). I don't understant why... but if i don't add <nom_Du_Pays> I obtain the Total Exportation for all country.

OmarBenSalem

Can you please share your QVF file?

So I can really understand your expressions and thus help you?

Thanks