Difference between Aggregated Fields

[chrisevans_cj]

Hi everyone ... looking for a bit of help here:

Sales Person	Date	Total Sales	Total Fruit	Total Veg
Bob	22 09 2018	110	55	55
Bob	23 09 2018	120	60	60
Bob	24 09 2018	175	65	115
Jimmy	22 09 2018	20	5	15
Jimmy	23 09 2018	20	5	15
Jimmy	24 09 2018	20	5	15
Sally	22 09 2018	170	115	55
Sally	23 09 2018	180	120	60
Sally	24 09 2018	190	125	65

my dataset returns aggregated values per day and i would like in my load statement add 3 new column which shows the difference of the Total Sales, Total Veg and Total Fruit on that day. So something like this:

Sales Person	Date	Total Sales	Total Fruit	Total Veg	Diff Sales	Diff Fruit	Diff Veg
Bob	22 09 2018	110	55	55	10	5	5
Bob	23 09 2018	120	60	60	10	5	5
Bob	24 09 2018	175	65	115	55	5	55
Jimmy	22 09 2018	20	5	15	0	0	0
Jimmy	23 09 2018	20	5	15	0	0	0
Jimmy	24 09 2018	20	5	15	0	0	0
Sally	22 09 2018	170	115	55	10	5	5
Sally	23 09 2018	190	125	65	20	10	10
Sally	24 09 2018	200	130	70	10	5	5

What is the best practice for this?

[rubenmarin]

Hi, if you have a table with Sales Person an Date as dimension you can use an expression like:

If(Above([Sales Person])=[Sales Person], Sum(Sales)-Above(Sum(Sales)))

What I don't know is how you get a diffference for the first value of each sales person as it doen't have any value to make to comparison.

[chrisevans_cj]

Hi,

i am looking to use this in the Data Extraction / Script Load portion. Your solution works in a graph however i need the data itself modified during ETL so i can do analysis on these daily figures, interact with those data fields and find abnormal values/behaviors

To answer your question "What I don't know is how you get a difference for the first value of each sales person as it doesn't have any value to make to comparison." 

This would be the difference from the previous day's data - at some point in time it would start with 0 as the Total Sales, it is because this was just a sample dataset - These new columns would basically show the difference between each day

[rubenmarin · Accepted solution]

Hi, you can use Peek() to access previous loaded record, you only need to do a sorted load and calculates the differences:

Data:
LOAD [Sales Person],	Date(Date#(Date, 'DD MM YYYY')) as Date,	Sales,	Fruit,	Veg Inline [
Sales Person,	Date,	Sales,	Fruit,	Veg
Bob,	22 09 2018,	110,	55,		55
Bob,	23 09 2018,	120,	60,		60
Bob,	24 09 2018,	175,	65,		115
Jimmy,	22 09 2018,	20,		5,		15
Jimmy,	23 09 2018,	20,		5,		15
Jimmy,	24 09 2018,	20,		5,		1,
Sally,	22 09 2018,	170,	115,	55
Sally,	23 09 2018,	180,	120,	60
Sally,	24 09 2018,	190,	125,	65
];

DataWithDiff:
LOAD *,
	If(Peek([Sales Person])=[Sales Person], Sales-Peek(Sales), Sales) 	as [Sales Diff],
	If(Peek([Sales Person])=[Sales Person], Fruit-Peek(Fruit), Fruit) 	as [Fruit Diff],
	If(Peek([Sales Person])=[Sales Person], Veg-Peek(Veg), Veg) 		as [Veg Diff]
Resident Data
Order By [Sales Person], Date;

DROP Table Data

[chrisevans_cj]

Thank you kind Sir. Have a great day - take care, wash your hands regularly, stay home and be safe out there