Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
;
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
;
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)
Did u try removing Column X and run it? Are you still facing issue?
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
;
thank you! it worked 🙂