Sum Two Columns

[CBoardy]

Hi All,

I want to sum two columns on the same table to make a third table in the Data Load Editor. I have been scouring the internet and have attempted buy doing this but not had any success:

NoConcatenate
PorkBelly:

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

I'll be repeating this step for Var = 'vs TGT' replacing num#(TGT....

Please Help

TIA

[JHuis]

Dear,

can you maybe give me a sample data what you are trying to archieve?

[marcus_sommer]

If ACT and PY are already valid interpreted numeric values you won't need the num#() converting. If they are not numbers a num#() must contain exactly the right form-pattern and by to the default-settings different decimal-delimiters als the second and third parameter of num#() needs to be specified. Such converting should be already done by the first load and not in following resident-loads.

[CBoardy]

@marcus_sommer  Thanks for clarifying if Num# was needed, they're numerical values so I'll take it out.

@JHuis  Heres an example

Month	BU	"Global BU",	Brand,	"P&L Category",	Var,	TGT,	ACT,	PY,	ACT-PY or ACT - TGT
01 JAN	Retail	BR	no brand	Sales	vs PY	0	1	2	1 - 2
01 JAN	Bio	Bio	Mcdonalds	Sales	vs PY	0	2	2	2-2
01 JAN	Retail	BR	Five Guys	Sales	vs PY	0	8	2	8-2
01 JAN	Retail	BR	no brand	Product Margin	vs TGT	1	5	0	5-1
01 JAN	Bio	Bio	Mcdonalds	Product Margin	vs TGT	2	7	0	7-2
01 JAN	Retail	BR	Five Guys	Product Margin	vs TGT	3	6	0	6-3

[anat]

can you try like .........

ACT-if(Var='vs PY',PY,TGT)

[Channa · Accepted solution]

num# is inputformat

NUM(Num#(ACT , '##.##'))- NUM(Num#(PY,'##.##'))

[AustinSpivey]

I think Anat’s answer is the correct one, though it looks like when [Var] is 'vs PY,' [TGT] is zero 0 and when [Var] is 'vs TGT,' [PY] is zero 0. So, if you just need to sum those fields, you can use RangeSum([TGT], [ACT], [PY]) or simply [TGT] + [ACT] + [PY]. Your example shows concatenation, which may just be for the sake of example, but if that’s the desired outcome then you can use the ampersand & symbol to do that, like ACT & '-' & if(Var='vs PY',PY,TGT).

[AustinSpivey]

Or if subtracting, then you could also do RangeSum([ACT], [TGT]*-1, [PY]*-1) or [ACT] - [TGT] - [PY] or [ACT] + ([TGT]*-1) + ([PY]*-1).

[CBoardy]

Thanks a lot for all your contributions.

It turned out my data model was incorrect in the first place which was the main issue

[E_Røse]

Did you try to add the columns Num#(ACT , '##.##') and Num#(PY,'##.##') separately first, so that you are certain that they are actually converted into numbers?

Please like and mark my answer as a solution, if it resolved your issue.