Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
num# is inputformat
NUM(Num#(ACT , '##.##'))- NUM(Num#(PY,'##.##'))
Dear,
can you maybe give me a sample data what you are trying to archieve?
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.
@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 |
can you try like .........
ACT-if(Var='vs PY',PY,TGT)
num# is inputformat
NUM(Num#(ACT , '##.##'))- NUM(Num#(PY,'##.##'))
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).
Or if subtracting, then you could also do RangeSum([ACT], [TGT]*-1, [PY]*-1) or [ACT] - [TGT] - [PY] or [ACT] + ([TGT]*-1) + ([PY]*-1).
Thanks a lot for all your contributions.
It turned out my data model was incorrect in the first place which was the main issue
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.