flag customers based on no of orders (orders source and month)

[ivory]

hello,

i don't know what function to use in qlik in order to flag customers based on criteria below

i need to delimit the moment when the client becomes an app user (e.g he uses the the mobile application to make orders)

for example, customer 214315 has orders since apr20- sept'21, until sept'20, the client ordered from site/ desktop etc, and in sept'20 switched to mobile app (has more orders in app than in site)

customers_id_assoc	AnLuna	Order Source new	Orders	x
214315	Apr-2020	other	1	n/a
214315	May-2020	other	1	n/a
214315	Jun-2020	other	3	n/a
214315	Jul-2020	other	4	n/a
214315	Aug-2020	other	1	n/a
214315	Sep-2020	app	2	app client
214315	Sep-2020	other	1
214315	Nov-2020	app	6	app client
214315	Dec-2020	app	1	app client
214315	Dec-2020	other	1	app client
214315	Jan-2021	app	2	app client
214315	Feb-2021	app	1	app client
214315	Mar-2021	app	2	app client
214315	Apr-2021	app	1	app client
214315	May-2021	app	2	app client
214315	Jun-2021	app	3	app client
214315	Jul-2021	app	3	app client
214315	Aug-2021	app	3	app client
214315	Sep-2021	app	5	app client

[Saravanan_Desingh]

Try something like this,

tab1:
LOAD * INLINE [
    customers_id_assoc, AnLuna, Order Source new, Orders, x
    214315, Apr-2020, other, 1, n/a
    214315, May-2020, other, 1, n/a
    214315, Jun-2020, other, 3, n/a
    214315, Jul-2020, other, 4, n/a
    214315, Aug-2020, other, 1, n/a
    214315, Sep-2020, app, 2, app client
    214315, Sep-2020, other, 1,  
    214315, Nov-2020, app, 6, app client
    214315, Dec-2020, app, 1, app client
    214315, Dec-2020, other, 1, app client
    214315, Jan-2021, app, 2, app client
    214315, Feb-2021, app, 1, app client
    214315, Mar-2021, app, 2, app client
    214315, Apr-2021, app, 1, app client
    214315, May-2021, app, 2, app client
    214315, Jun-2021, app, 3, app client
    214315, Jul-2021, app, 3, app client
    214315, Aug-2021, app, 3, app client
    214315, Sep-2021, app, 5, app client
];

Left Join(tab1)
LOAD customers_id_assoc, Text(Date(Min(Date#(AnLuna,'MMM-YYYY')),'MMM-YYYY')) As AnLuna, 
	'app' As [Order Source new],'Y' As Delimit_Moment
Resident tab1
Where [Order Source new]='app'
Group By customers_id_assoc
;

[ivory]

hi, it doesn't work,  I don't have the last column (x) in the database, I included it in the example to make it easier to explain what I need, sorry for the confusion

also, i have to take into consideration no of orders (if in one month a client has for example 5 orders and 2 are from mobile app and 3 from site, it's not an app client yet)

[Saravanan_Desingh]

Did u try removing Column X and run it? Are you still facing issue?

[Saravanan_Desingh · Accepted solution]

Please check this,

tab1:
LOAD * INLINE [
    customers_id_assoc, AnLuna, Order Source new, Orders
    214315, Apr-2020, other, 1
    214315, May-2020, other, 1
    214315, Jun-2020, other, 3
    214315, Jul-2020, other, 4
    214315, Aug-2020, other, 1
    214315, Sep-2020, app, 2
    214315, Sep-2020, other, 1  
    214315, Nov-2020, app, 6
    214315, Dec-2020, app, 1
    214315, Dec-2020, other, 1
    214315, Jan-2021, app, 2
    214315, Feb-2021, app, 1
    214315, Mar-2021, app, 2
    214315, Apr-2021, app, 1
    214315, May-2021, app, 2
    214315, Jun-2021, app, 3
    214315, Jul-2021, app, 3
    214315, Aug-2021, app, 3
    214315, Sep-2021, app, 5
];

Left Join(tab1)
LOAD customers_id_assoc, AnLuna, 
	If(Sum(If([Order Source new]='app',Orders))>=Sum(If([Order Source new]<>'app',Orders)),'Y') As IsApp
Resident tab1
Group By customers_id_assoc, AnLuna
;

Left Join(tab1)
LOAD customers_id_assoc, Text(Date(Min(Date#(AnLuna,'MMM-YYYY')),'MMM-YYYY')) As AnLuna, 
	'app' As [Order Source new],'Y' As Delimit_Moment, 'Y' As IsApp
Resident tab1
Where [Order Source new]='app'
Group By customers_id_assoc
;

[ivory]

thank you! it worked 🙂