topic Re: flag customers based on no of orders (orders source and month) in QlikView

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

ivory — Thu, 30 Sep 2021 13:03:51 GMT

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

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

Saravanan_Desingh — Thu, 30 Sep 2021 13:28:26 GMT

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 ;

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

ivory — Thu, 30 Sep 2021 14:07:16 GMT

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)

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

Saravanan_Desingh — Thu, 30 Sep 2021 14:03:42 GMT

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

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

Saravanan_Desingh — Thu, 30 Sep 2021 18:41:20 GMT

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 ;

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

ivory — Fri, 01 Oct 2021 07:08:34 GMT

thank you! it worked 🙂