Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
In the load script:
Concatenate (Table)
Load 'Avg' as Field,
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.
In the load script:
Concatenate (Table)
Load 'Avg' as Field,
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.
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)
Load 'Avg' as Field,
Year
sum(a)/count(a) as a,
sum(b)/count(b) as b,
sum(c)/count(c) as c
Resident Table
Group by Year;