Inserting a calculated row to a table

[CBoardy]

Hi All,

I want to load a calculated row on a table within qlik sense. The two fields being used is Sales & Margin and I would like to create a row that calculates the % of Sales (Margin/Sales), there are 3 parameters involved - Actual, Target, Prior Year - ACT , TGT, PY. Is this possible to create? Attached is what I've tried but I've not got it to work. P&L Category is the Dimension that holds 'Margin' & 'Sales', Var is the Dimension that contains TGT ACT PY and Values is the Values. I am able to extract the data successfully but I've had a request to include this in the Table instead of it being a separate KPI and this is the part I'm struggling at. 

[Calculations]:
LOAD * Inline [
P&L Category, Var, Values
% Margin of Sales, TGT, (Sum({<[P&L Category]={'Margin'}, [Var]={'TGT'}>}Values)/Sum({<[P&L Category]={'Sales'}, [Var]={'TGT'}>}Values))
% Margin of Sales, ACT,
% Margin of Sales, PY
];

[neerajthakur]

Try this : 

if([P&L Category]='Margin' and [Var]='TGT',Sum(Values)) as Margin_Value,

if([P&L Category]='Sales' and [Var]='TGT',Sum(Values)) as Sales_Value

Now make a resident load of above table and use this Margin_Value/Sales_Values as XYZ

Hope this helps, if it solves your problem accept it as solution.

[neerajthakur · Accepted solution]

In this you can also use preceding load 

Load 

if([P&L Category]='Margin' and [Var]='TGT',Sum(Values)) as Margin_Value,

if([P&L Category]='Sales' and [Var]='TGT',Sum(Values)) as Sales_Value;

Load *, Margin_Value/Sales_Values as XYZ From -------;

[CBoardy]

@neerajthakur Awesome thanks!

I want to now delete all rows where Var = 'TGT' or 'ACT' or 'PY' 

Do you know how I do this in the Data load editor? 

It would be a massive help! TIA

[neerajthakur]

Yep, we can do it. 

Use this

NoConcatenate

NewTable:

Load

*

Resident TableName

where Var<>'TGT' or Var<>'ACT' or Var<>'PY';

Drop Table TableName;

[CBoardy]

Thanks a lot

[CBoardy]

@neerajthakur  Hi one last Question sorry! I need to add two columns to make a third measure in data load how would I do that? 

Trying: 

Load
"Reporting Period (Month)",
BU,
"Global BU",
Brand,
"P&L Category",
Var,
ACT,
PY,
TGT,
Num#(ACT , '##.##')- Num#(PY,'##.##') as Values
Resident [P&L Table] Where Var='vs PY';

but the values aren't pulling through