Customer Shifting Behaviour in Qlik Sense

[kushagra_jain]

Hello Everyone,

I am working on some advance analysis and not sure how to get this done in qliksense. I am working to get the no of customers who moved or shifted from Service S1 to S2 and ViceVersa.

My dataset has these relevant fields ClientName, DateofOrder and Service(Containing S1 or S2) as posted in the photo.

For this analysis I want to take the last order date of respective services by a particular client, from this example what I want is :

No of clients from S1 to S2 : 2 (JKL as his last S1 order was on 3/19/2019 and S2 order was on 3/26/2019 so he moved to S1 to S2 and similarly for XYZ, Please NOTE ABC will not be counted because of the last order date of respective services)

No of clients from S2 to S1 : 2 (ABC as his last S1 order was on 3/21/2019 and S2 order was on 3/15/2019 so he moved to S2 to S1 and similarly for PQR, Please NOTE JKL will not be counted because of the last order date of respective services)

I am not sure how to start with this analysis, Please help me out.

Asking for your help again @sunny_talwar 

Thank You

[OmarBenSalem · Accepted solution]

Try to adapt it to what u have : 

urTable:
load ClientName, date(date#( DateofOrder0,'M/DD/YYYY'),'M/D/YYYY') as DateofOrder,Service
Inline [
ClientName, DateofOrder0,Service
ABC ,3/14/2019, s1
ABC,3/15/2019,s2
PQR,3/16/2019,s2
PQR,3/17/2019,s1
JKL,3/18/2019,s2
JKL,3/19/2019,s1
GHI,3/20/2019,s2
ABC,3/21/2019,s1
PQR,3/22/2019,s1
XYZ,3/23/2019,s2
XYZ,3/24/2019,s1
XYZ,3/25/2019,s1
JKL,3/26/2019,s2

];

left Join(urTable)
load ClientName , date(max(DateofOrder),'M/D/YYYY') as MaxDateS1 Resident urTable Where Service='s1' Group by ClientName;

left Join(urTable)
load ClientName , date(max(DateofOrder),'M/D/YYYY') as MaxDateS2 Resident urTable Where Service='s2' Group by ClientName;

NoConcatenate

load ClientName,DateofOrder,Service, if(MaxDateS1>MaxDateS2,'S2 to S1','S1 to S2') as Behaviour Resident urTable;
Drop Table urTable;

result

[sunny_talwar]

Where exactly are you looking to view this information in a chart or do you just need the count in a kpi object?

[kushagra_jain]

I just need the Count of KPI as how many moved from S1 to S2 and another KPI for Count of People from S2 to S1.

[OmarBenSalem]

Have u taken 2 seconds to read my response?..

[kushagra_jain]

Hi OmarBenSalem,

Yes I have looked at your solution and really appreciate you for taking out time to answer my query. Pardon me for not replying to your post as I was trying to understand your logic and implement as the count in KPI object.

I guess what you are doing is Aggregating on Service S1, Client Name and Max DateofOrder and then joining with the aggregate of Service S2, Client Name and Max DateofOrder and then Applying the condition on Max DateofOrder for S1 and S2 to create the Behaviour logic and then drew the graph for movement.

The point where I had trouble in implimenting is :
1) Inside the Data Load editor, as I am not sure how the sql queries works and how to include inside Data Load Editor as the data that I have given you is an example so there are a lot of records and other parameters, so I am getting the data as
[Client_data]:
LOAD
[ClientName],
[DateofOrder],
[Status],
[Fees],
.
.
[Service];
SQL SELECT ''ClientName",
"DateofOrder",
"Status",
"Fees",
.
.
"Service"
FROM "database"."owner"."some_table";

So i generated new script and pasted your solution and moified it to
[urTable]:
load ClientName, date(date#( DateofOrder0,'M/DD/YYYY'),'M/D/YYYY') as DateofOrder,Service;
left Join([urTable])
load ClientName , date(max(DateofOrder),'M/D/YYYY') as MaxDateS1 Resident urTable Where Service='s1' Group by ClientName;
left Join([urTable])
load ClientName , date(max(DateofOrder),'M/D/YYYY') as MaxDateS2 Resident urTable Where Service='s2' Group by ClientName;
load ClientName,DateofOrder,Service, if(MaxDateS1>MaxDateS2,'S2 to S1','S1 to S2') as Behaviour Resident urTable;
Drop Table urTable;
I am getting error as UrTable not found on first leftjoin statement (I am figuring out how to fix this).

2) I had one more concern as even if the query works, how am I gonna get the table as you are droping the table (Please pardon my sql knowledge).