Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Use a Generic load to split your categories into columns then join back together.
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;
Use a Generic load to split your categories into columns then join back together.
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;