What is the best way to analyze profitability of related, but separate transactions? 7

[montypython1]

Greetings,

What is the best way to analyze profitability of related, but separate transactions?
Specifically, I am trying to analyze the profitability of vehicle sales transactions, but I would like to include the subsequent trade-in transactions.

It is very common for a car dealership to sell a new vehicle and take the customers old vehicle in as a trade-in. They may not make much money on the new vehicle transaction, but they may make a lot of money on the subsequent sale of the older trade-in vehicle. My objective is to connect these 2 (or possibly 3 or 4) separate transactions together to analyze the profitability of entire string of related transactions.

Every vehicle sold, whether new or used, contains a unique stock number (ex: "C77118"). If a customer buys a vehicle and trades in their old vehicle, then that stock number (ex: "C77118A") is included within the original transaction. If the trade-in vehicle is subsequently sold, and there is a trade-in (ex: "C77118B") on the second transaction, then the cycle should continue. I want to collect the profitability of each transaction, then summarize them together.

I am attaching a very small CSV data file that I am using for testing purposes.

If anyone has any ideas, I would certainly be grateful.

Thank you,
Dave

 

[montypython1]

MK and Olaf,

Thank you! Your solutions are very close to solving my problem.

MK, you brought up a valid concern when you said:

{if} the data is "pregrouped" in the way you say and that the number of records is reasonable (cf. size of array in VFP)

I can presort the data before using your code, but notice stock number "K4376A". In a larger data set, there would likely be another "non-related" stock number between "K4376A" and the trade-in "TI1234". In my testing, it seems to cause a break in the logic.

It appears that your code WILL work for many dealerships, just not those with dissimilar stock numbers on the trade-ins.

Still, I am MUCH closer to a solution than before.

Thank you for your input!

Dave

 

[Olaf Doschke]

If the data isn't sorted that way, then first find out root records (where the Sold_StkNum never occures beforehand as TradeIn_StkNu) and then instead of scan or SKIP 1, you LOCATE for the next transaction until you find none.

Root records are

SELECT * from yourtable where Sold_StkNum NOT IN (Select TradeIn_StkNum FROM yourtable)

Bye, Olaf.

 

[Olaf Doschke]

By the way it's easier to backtrack leaf nodes = last sales in such a concatenated list:

SELECT * FROM yourtable where EMPTY(TradeIn_StkNum)

But your lists may not always have such a last sale without a trade-in car.

You would then track back until you get to a Sold_StkNum not being any previous TradeIn_StkNum, and then would return from this root to the leaf again to populate the common root field.

Bye, Olaf.

 

[brigmar]

Based on a minimal number of fields: salestk, profit, tradeinstk


[tt]SELECT salestk as rootstk,* FROM deals INTO CURSOR temp1 READWRITE
DO WHILE _TALLY>0
UPDATE t1 SET t1.rootstk=t2.rootstk from temp1 t1 inner join temp1 t2 on t1.salestk=t2.tradeinstk AND t2.rootstk!=t1.rootstk
ENDDO
SELECT rootstk,COUNT(*),SUM(profit) FROM temp1 GROUP BY 1 INTO CURSOR temp2[/tt]


temp1 now contains the rootstk, which is the 'starting' stock item in a chain.
temp2 is your profitability summary

 

[Olaf Doschke]

These updates could take a while, better index on rootstk, salesstk and tradeinstk before going into the while loop. Otherwise this is elefant.

Bye, Olaf.

 

[Olaf Doschke]

f->g

 

[vgulielmus]

An elegant elefant (elephant)
Sorry, but I couldn't help myself...

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

Yes, and in german elephant is Elefant. They are elegant animals, aren'T they? They have Ivory, german word for it is Elfenbein, which translated back means elves bones. And who is more elgeant than elves? Just think about Liv Tyler in The Lord of the Rings...

To get back to the topic: The indexing is of course not needed for the sample of 8 records. But I assume you're postprocessing a whole bunch more deals of all the lifetime of your car sales shop and that'll best be done with indexes.

Bye, Olaf.

 

[montypython1]

Hi Brigmar and Olaf,

Many, many thanks! You guys are amazing to me.
This now does exactly what I needed. Very clean and elegant solution.

Thanks so much for your help.

Dave