Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
CBoardy
Contributor III
Contributor III

Field1 - Field2 = Field3

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

Labels (1)
4 Replies
CBoardy
Contributor III
Contributor III
Author

is an example:
CBoardy_0-1677778950888.png

"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
Vegar
MVP
MVP

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];

 

CBoardy
Contributor III
Contributor III
Author

@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

marcus_sommer

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.