13 Replies Latest reply: Mar 31, 2017 9:06 AM by omar bensalem

# 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

• ###### Re: Merge cells or Similary Merge cells

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..

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:

See the attached app

• ###### Re: Merge cells or Similary Merge cells

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 :

, 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 ?

• ###### Re: Merge cells or Similary Merge cells

Like this?

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

• ###### Re: Merge cells or Similary Merge cells

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 !

• ###### Re: Merge cells or Similary Merge cells

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?

avg({1} total Market)

See the attached app:

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

Hope this helps?

• ###### Re: Merge cells or Similary Merge cells

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)

if I select Allemagne value

Do you have an explain ?

I'm sorry for this questions...

• ###### Re: Merge cells or Similary Merge cells

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:

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

• ###### Re: Merge cells or Similary Merge cells

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.

• ###### Re: Merge cells or Similary Merge cells

Thanks

• ###### Re: Merge cells or Similary Merge cells

Yes, of course. Can you explain me how I can do that ?

• ###### Re: Merge cells or Similary Merge cells

Then:

I'll see it and answer you in about an hour and a helf.

It's my lunch time

• ###### Re: Merge cells or Similary Merge cells

No, how I can extract a qvf file from QlikSense ? I will be at my home within eigth hours, so I send you that within eigth hours.

Thank you and good lunch ^^ !

• ###### Re: Merge cells or Similary Merge cells

Ah ok, you'll find the .qvf file under :

C:\Users\YourUser\Documents\Qlik\Sense\Apps