topic Re: Field1 - Field2 = Field3 in App Development

Field1 - Field2 = Field3

CBoardy — Thu, 02 Mar 2023 17:40:40 GMT

Hi All,

I need to sum two fields together to make another field in data load editor. I've been trying a whole bunch of things but no success - I think im close with:

abc:

Load
"Reporting Period (Month)",
BU,
"Global BU",
Brand,
"P&L Category",
Var,
Sum([ACT]-[PY]) as Values
Resident [P&L Table]
Group By
"Reporting Period (Month)",
BU,
"Global BU",
Brand,
"P&L Category",
Var,
;
The only Numerical fields are the ones I'm try to manipulate (ACT, PY, TGT and Values) and I am stuck I want to calculate the difference i.e 'vs PY' is Actual (ACT) - Prior Year (PY)

Someone Please help

Re: Field1 - Field2 = Field3

CBoardy — Thu, 02 Mar 2023 17:45:07 GMT

is an example:

"Reporting Period (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

Re: Field1 - Field2 = Field3

Vegar — Thu, 02 Mar 2023 22:36:31 GMT

I see a syntax error. You should not have a comma after the last group by feld.

abc:

Load

"Reporting Period (Month)",

BU,

"Global BU",

Brand,

"P&L Category",

Var,

Sum([ACT]-[PY]) as Values

Resident [P&L Table]

Group By

"Reporting Period (Month)",

BU,

"Global BU",

Brand,

"P&L Category",

Var

;

Consider if you really need to use the group by. Maybe this is enough?

abc:

Load

"Reporting Period (Month)",

BU,

"Global BU",

Brand,

"P&L Category",

Var,

[ACT]-[PY] as Values

Resident [P&L Table];

Re: Field1 - Field2 = Field3

CBoardy — Fri, 03 Mar 2023 09:45:00 GMT

@Vegar thanks for getting back to me.

Think it needs the group by otherwise error saying invalid expression.

It's so annoying I need to figure this out its the last piece of the puzzle

AHHHHH

Re: Field1 - Field2 = Field3

marcus_sommer — Fri, 03 Mar 2023 11:14:36 GMT

It wasn't meant only to remove the group by statement else the entire aggregation within the load - means all aggregation-functions like sum(), count() and the group by statement.

In many cases are aggregations within the script not sensible because it creates a fixed data-set (against the specified dimensions) by loosing the underlying granularity. If now not only this view is needed else various other ones you would need a separate aggregation for each one.

Further aggregations against multiple fields may not mandatory reduce the remaining data-set significantly and may have therefore only low benefits within the UI performance. Therefore the usage of aggregations should be well balanced - efforts against benefits related to possible side-effects.