classify the sales proposal as won/lost based on an event log

[FarmerTom]

Hello,

I have a simple timeseries data representing a sales event log. I would like to create a new column in script editor that classifies the given Ops id as WON, LOST or PENDING based on the below conditions:

IF for a given Ops id a "Deal signed" milestone exists, the new column (status) should return WON for all of the Ops id records

IF for a given Ops id a "Opportunity cancelled" milestone exists, the new column should return LOST for all of the Ops id records

IF none of the above, then new column sould return PENDING

Excel example attached, will appreciate any advice

[GaryGiles · Accepted solution]

Try the following load script:

WonOrLost:
LOAD
[Ops id],
Milestone,
Date(Time,'MM/DD/YYYY') as Time,
[Sales Manager],
[Client ID],
Join,
Status
FROM [lib://QlikSenseApps/WONorLOST.xlsx]
(ooxml, embedded labels, table is Sheet1);

Ops:
Load distinct [Ops id]
Resident WonOrLost;

Join (Ops)
Load [Ops id],
max(Time) as DealWon
Resident WonOrLost
where Milestone = 'Deal signed'
group by [Ops id];

join (Ops)
Load [Ops id],
max(Time) as OpportunityCanceled
Resident WonOrLost
where Milestone = 'Opportunity cancelled'
group by [Ops id];

join (Ops)
Load [Ops id],
if(not isnull(DealWon),'Won',if(not isnull(OpportunityCanceled),'Lost','Pending')) as Classification
Resident Ops;

drop fields DealWon, OpportunityCanceled;

//The following code will add the field by to the WonOrLost table.  If you have an Ops dimension table, you may want to add it to that table instead.

Left join (WonOrLost)
Load [Ops id],
Classification
Resident Ops;

drop table Ops;

[FarmerTom]

Works like a charm, thanks!