Create a resident table to compare current and previous week

[martin_hamilton]

Hi

I wish to create a resident table which summarizes invoice information which has been previously loaded into a resident table which creates a current week value and previous week value allowing me to compare and include within an extension I am using.

As part of the load I am also including the week number (using AutoNumber(weekyear(INVOICE_DATE) &'|' & week(INVOICE_DATE)) as WeekSerial)

CUSTOMER_TYPE	INVOICE_AMOUNT	INVOICE_DATE	WeekSerial
Type A	908	01/01/2018	1
Type B	6466	01/01/2018	1
Type C	63	01/01/2018	1
Type D	256636	01/01/2018	1
Type A	98798	08/01/2018	2
Type B	34235	08/01/2018	2
Type C	25676	08/01/2018	2
Type D	74747	08/01/2018	2
Type A	2525	15/01/2018	3
Type B	63678	15/01/2018	3
Type C	856	15/01/2018	3
Type D	5799	15/01/2018	3
Type A	54574	23/01/2018	4
Type B	848989	23/01/2018	4
Type C	3633	23/01/2018	4
Type D	4367	23/01/2018	4

I want the resident table to group by CUSTOMER_TYPE but only including the Current & Previous Weeks summed totals.

So assuming we are in week 4 of the month I would expect the following in a new resident table, I dont know how to work out the previous week or even select that to summarise into a column.

CUSTOMER_TYPE	CURRENT_WEEK	PREVIOUS_WEEK
TYPE_A	54574	2525
TYPE_B	848989	63678
TYPE_C	3633	856
TYPE_D	4367	5799

Is this even the best way to do it?

Any help appreciated.

Thanks

Martin

[sunny_talwar · Accepted solution]

May be try this

Table:

LOAD * INLINE [

    CUSTOMER_TYPE, INVOICE_AMOUNT, INVOICE_DATE, WeekSerial

    Type A, 908, 01/01/2018, 1

    Type B, 6466, 01/01/2018, 1

    Type C, 63, 01/01/2018, 1

    Type D, 256636, 01/01/2018, 1

    Type A, 98798, 08/01/2018, 2

    Type B, 34235, 08/01/2018, 2

    Type C, 25676, 08/01/2018, 2

    Type D, 74747, 08/01/2018, 2

    Type A, 2525, 15/01/2018, 3

    Type B, 63678, 15/01/2018, 3

    Type C, 856, 15/01/2018, 3

    Type D, 5799, 15/01/2018, 3

    Type A, 54574, 23/01/2018, 4

    Type B, 848989, 23/01/2018, 4

    Type C, 3633, 23/01/2018, 4

    Type D, 4367, 23/01/2018, 4

];

Left Join (Table)

LOAD Max(WeekSerial) as Max,

Max(WeekSerial, 2) as Max2

Resident Table;

AggrTable:

LOAD CUSTOMER_TYPE,

Sum(If(WeekSerial = Max, INVOICE_AMOUNT)) as CURRENT_WEEK,

    Sum(If(WeekSerial = Max2, INVOICE_AMOUNT)) as PREVIOUS_WEEK

Resident Table

Group By CUSTOMER_TYPE;

[martin_hamilton]

Happy Friday Sunny! Many Thanks exactly what I was looking for.