Hello All, I have a call center data, and we have customers with certain stage like stage 1,2,3,4,5 and all the stages has certain criteria to pass on to next stage, refer below criteria, StageOutcome 1Outcome 2Outcome 3Outcome 4Stage 1DC PIN setupIB registrationMB registration Stage 2Salary mandateUPI registrationDC txn activationFamily accountStage 3IB txn activationUPI txn activation Stage 4Billpay setupCC cross-sell Stage 5Demat cross-sellReferral benefits Now for example user is in stage1 then he have to satisfy below criteria to move to next stage i.e stage 2 DC PIN setupIB registrationMB registration and same for all the stages, Please help me out with a formula that can do it, Please let me know if you need anymore insights. Thanks in advance.

Hi Qiu, Sorry for the confusion, Here are the files. LCMUPDATE_dummydata.xlsx NTB_DATA_dummydata.xlsx LCM repository file.xlsx Final p4 and p5.yxmd Archetype and stage wise outcomes.xlsx

@Adithya_S You said all the related files, but I can only see one Excel file. Please confirm.

Next stage calculation using current stage and certain criteria

Adithya_S

Hello All,

I have a call center data, and we have customers with certain stage like stage 1,2,3,4,5

and all the stages has certain criteria to pass on to next stage, refer below criteria,

Stage	Outcome 1	Outcome 2	Outcome 3	Outcome 4
Stage 1	DC PIN setup	IB registration	MB registration
Stage 2	Salary mandate	UPI registration	DC txn activation	Family account
Stage 3	IB txn activation	UPI txn activation
Stage 4	Billpay setup	CC cross-sell
Stage 5	Demat cross-sell	Referral benefits

Now for example user is in stage1 then he have to satisfy below criteria to move to next stage i.e stage 2

DC PIN setup

IB registration

MB registration

and same for all the stages,

Please help me out with a formula that can do it,

Please let me know if you need anymore insights.

Thanks in advance.

Accepted answers

All comments

Emil_Kos

Hi @Adithya_S,

I suggest using combination of the Concatiation + Find and replace tool.

This will work if the outcomes are always in the same sequence. If that would not be the case we would need to add a transpose tool first and sort the data in the correct way.

Please let me know if this was helpful for you.

I wouldn't advise using formula tool only as this will be problematic for you to maintain.

Find and replace.yxmd

Qiu

@Adithya_S
Can you provide a sample of call center data?

Adithya_S

Hi Qiu,

Sample file attached.

LCM repository file.xlsx

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335