Adding Calculations into a field

[CBoardy]

Hi All,

I have a Dimension with the two Values, Sales & Product Margin and a Measure field called Values. I need to calculate %(Margin/Sales).  There are 5 other dimensions which classify every value example is attached. I need to add a calculations into the table and I believe I need to duplicate the table, then replace the category with '% Margin of Sales' &  value which is calculated by Margin/Sales . Once all that is completed concatenate the two tables together to make a single table. The highlighted value is what I need to produce.

Help is very appreciated

[ogster1974 · Accepted solution]

Use a Generic load to split your categories into columns then join back together.

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/...

TMP:
load * Inline [
Month, BU, Brand, Ver, Category, Values
Jan, Retail, x, ACT, Sales, 1
Feb, Bio, y, TGT, Sales, 2
Mar, Retail, z, PY, Sales, 3
Jan, Retail, x, ACT, Margin, 4
Feb, Bio, y, TGT, Margin, 5
Mar, Retail, z, PY, Margin, 6
];

HDR:
Load
Month&'|'&BU&'|'&Brand&'|'&Ver as Key,
Month,
BU,
Brand,
Ver,
Category,
Values
Resident TMP
;

Drop TABLE TMP;

VAL:
Generic Load *;
Load
Key,
Category,
Values
Resident HDR
;

NoConcatenate
JOIN:
Load *
Resident HDR;
Inner Join (JOIN)
Load *
Resident VAL.Margin;
Inner Join (JOIN)
Load *
Resident VAL.Sales;

Drop Tables HDR, VAL.Margin, VAL.Sales;

NoConcatenate
FINAL:
Load
*,
Sales/Margin as [% Margin of Sales]
Resident JOIN;

Drop Table JOIN;