Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

martin_hamilton
Contributor

Create a resident table to compare current and previous week

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_TYPEINVOICE_AMOUNTINVOICE_DATEWeekSerial
Type A90801/01/20181
Type B646601/01/20181
Type C6301/01/20181
Type D25663601/01/20181
Type A9879808/01/20182
Type B3423508/01/20182
Type C2567608/01/20182
Type D7474708/01/20182
Type A252515/01/20183
Type B6367815/01/20183
Type C85615/01/20183
Type D579915/01/20183
Type A5457423/01/20184
Type B84898923/01/20184
Type C363323/01/20184
Type D436723/01/20184

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_TYPECURRENT_WEEKPREVIOUS_WEEK
TYPE_A545742525
TYPE_B84898963678
TYPE_C3633856
TYPE_D43675799

Is this even the best way to do it?

Any help appreciated.

Thanks

Martin

1 Solution

Accepted Solutions

Re: Create a resident table to compare current and previous week

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;

2 Replies

Re: Create a resident table to compare current and previous week

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
Contributor

Re: Create a resident table to compare current and previous week

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

Community Browser