topic Re: Difference between Aggregated Fields in QlikView

Difference between Aggregated Fields

chrisevans_cj — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Difference between Aggregated Fields

rubenmarin — Thu, 16 Apr 2020 14:03:07 GMT

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.

Re: Difference between Aggregated Fields

chrisevans_cj — Fri, 17 Apr 2020 04:34:37 GMT

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

Re: Difference between Aggregated Fields

rubenmarin — Fri, 17 Apr 2020 06:40:16 GMT

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

Re: Difference between Aggregated Fields

chrisevans_cj — Fri, 17 Apr 2020 09:31:25 GMT

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