# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
New Contributor III

Hi!

I need to create a table which simply looks like this:

In this table, i already have a dimension which comes from sql query and measures as a, b and c. Think this dimension as company brands, i have 100-110 distinct brand values.

What i want to do is to show averages, averages of specific brand groups and something like these. I know how to calculate it by using set analysis but what i couldn't achieve yet is that creating custom dimension values and adding them to already loaded dimension, not as a new dimension. Is it possible to be done in qlik sense?

Labels (3)

• ### Table Transformation

1 Solution

Accepted Solutions
Contributor

Concatenate (Table)

sum(a)/count(a) as a,

sum(b)/count(b) as b,

sum(c)/count(c) as c

Resident Table;

That will create the "Avg" record. You can repeat for the other records depending on what the actual logic needed.

4 Replies
Contributor

Concatenate (Table)

sum(a)/count(a) as a,

sum(b)/count(b) as b,

sum(c)/count(c) as c

Resident Table;

That will create the "Avg" record. You can repeat for the other records depending on what the actual logic needed.

New Contributor III

Thank you for the answer. It helped me a lot. But now i am facing with another problem. I need to use these concatenated rows as yearly analysis. Since we can't use set analysis in script i am not able to tell to qlik to get averages of max(year)-1. Is there a way to use set analysis logic inside the script?
Contributor

Assuming Year is in the table, the load below will calculate an average per year which you can then reference in set analysis.  If Year is not in the table then it becomes a data model question - you may need to add in a field that represents year but doesn't conflict with where year is stored for general use eg a calendar table.

Also please not that these 'group by' clauses are slow to process and use should be minimised.

Concatenate (Table)

Year

sum(a)/count(a) as a,

sum(b)/count(b) as b,

sum(c)/count(c) as c

Resident Table

Group by Year;

New Contributor III