Break-even point from loss of transaction on re-occurring income

[ElPantera]

Good Day, All!

I hope someone can be of assistance, on my challenge at hand!

I have data from two different sources, the Serial field is the linking field here.

1 = ERP:

This is the actual sale of the product on the ERP system under the invoice in which a loss is made here in the example below;

ERP:

Invoice Number	Transaction Date	Serial	Revenue	Cost	Profit
IN-000005301	1/2/2018 12:00:00 AM	14288661748	200	268	-68

2 = Re-Occurring Income:

The Product creates a revenue source every month it is used thereafter.

Date	Serial	Monthly Revenue
07/02/2018	14288661748	91
08/01/2018	14288661748	27

So what I have been trying to accomplish is join the re-occurring income onto the ERP system so I will be able to see how long it has taken to recover the loss on the sale from the monthly income. 

So if we do a join we get the below result, there are two lines where the Revenue, Cost, and Profit are being created against both the date field values where it should ideally have a single value for the fields Revenue, Cost, and Profit on the initial transaction date.

Serial	Revenue	Cost	Profit	Monthly Revenue	Actual Profit	Transaction Date	Date	Days to Break Even
14288661748	200	268	-68	91	23	1/2/2018 12:00:00 AM	07/02/2018	181
14288661748	200	268	-68	27	-42	1/2/2018 12:00:00 AM	08/01/2018	211

Maybe there is a way to do this in the front end while keeping these tables separate? 

I look forward to your input!

Regards

[Nicole-Smith]

I would keep the tables separate on the back-end and join them using a concatenated key that only looks at month and year because your date fields in each table do not align.

ERP:
AutoNumber(Serial & '|' & Month([Transaction Date]) & '|' & Year([Transaction Date]), 'Serial Month Year') AS Key

Re-Occurring Income:
AutoNumber(Serial & '|' & Month(Date) & '|' & Year(Date), 'Serial Month Year') AS Key

After you create the key, you will need to drop the Serial field from one of the two tables (I think you would want to drop it from the Income table).  Then both tables should link only on the Key field.

[ElPantera]

Hi @Nicole-Smith 

Thank you for your reply. The Key works perfectly in translating the Actual Profit, for say the month. The problem here is if a single serial is selected we wouldn't see the transactional information and rather just the information from the sale.

I need to be able to solve the time it has taken/number of days for it to reach the break-even point i.e Transaction Date - Date which in Qlik would be,

fabs(Date(Transaction Date,'MM/DD/YYYY')) - fabs(Date([Date],'MM/DD/YYYY'))

Based on the below would be 07/02/2018 less 1/2/2018.

Serial	Revenue	Cost	Profit	Monthly Revenue	Actual Profit	Transaction Date	Date	Days to Break Even
Totals	200.00	268.21	-68.21	117.88	49.67
14288661748	200.00	268.21	-68.21	.00	-68.21	-	1/2/2018 12:00:00 AM	-
-	.00	.00	.00	91.28	91.28	07/02/2018	-	-
-	.00	.00	.00	26.59	26.59	08/01/2018	-	-

[ElPantera]

Hi All,

So I am thinking of working towards a concatenate where my ERP System profit/loss value is the initial point at the date and then the re-occurring income concatenates to give the progress of the "recovery" so looks something like this.

Serial	Transaction Date	Revenue	Source
14288661748	01/02/2018	-68.21	ERP
14288661748	07/02/2018	91.28	re-occurring
14288661748	08/01/2018	26.59	re-occurring

I am going to try something along the lines of a peek and accumulation to see give the date point in which it moves over the zero break-even mark.

Let me know if anyone has ideas on this side!

[ElPantera]

With Concatenate I have done the following,

Serial	Transaction Date	Revenue	Profit Trend	Previous Date
14288661748	01/02/2018	-68.21	-68.21	-
14288661748	07/02/2018	91.28	23.07	07/02/2018
14288661748	08/01/2018	26.59	49.67	08/01/2018

Here we can see the product breaks-even in 07/02/2018 so we want to go and pull the difference between 07/02/2018 and 01/02/2018 which is 181 days. 

So I created a temp table from the fact table as below to calculate the difference as below,

TMP:
Load *,
if([Serial]=Previous([Serial]),
fabs(Date([Transaction Date],'MM/DD/YYYY')) - fabs(Date(Previous([Transaction Date]),'MM/DD/YYYY'))) as DiffDate
Resident Fact;

BreakEven:
Load
RowNo(),
[Meter Number],
DiffDate,
if([Serial]=Previous([Serial]),
if(sum([Revenue])>0,Peek('DiffDate',-1))) as DateTest
Resident TMP
Group By [Serial],DiffDate;
Drop Table TMP;

This gives us a table with the results below which links to the main table through the Serial field.

Meter Number	DateTest
14288661748	181

Awesome times! So I deiced to throw another Product in there with its unique Serial code to track it as well and this was the result,

Serial	Transaction Date	Revenue	Profit Trend	Previous Date
14288661748	01/02/2018	-68.21	-68.21	-
04263552905	04/19/2018	-394.38	-462.59	-
14288661748	07/02/2018	91.28	-371.31	-
14288661748	08/01/2018	26.59	-344.72	-

So the result is obviously not grouping/looking at the specific Serial and now looking at the Revenue field as the as combined across both of them. I was hoping the "if([Serial]=Previous([Serial])" used before the peek would  keep it looking at just the specific Serials. 

Any ideas clever people?

[Nicole-Smith · Accepted solution]

Try running the following (I think I have it doing what you want it to do):

SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY hh:mm:ss[.fff]';

ERP:
LOAD *, [Transaction Date] AS Date, Profit AS [Monthly Revenue] INLINE [
Invoice Number,Transaction Date,Serial,Revenue,Cost,Profit
IN-000005301,1/2/2018,14288661748,200,268,-68
IN-000005302,2/5/2018,04263552905,300,600,-300
];

CONCATENATE (ERP)
LOAD * INLINE [
Date,Serial,Monthly Revenue
7/2/2018,14288661748,91
8/1/2018,14288661748,27
4/19/2018,04263552905,400
6/22/2018,04263552905,400
];

Final:
LOAD Date, Serial, [Monthly Revenue],
	If(Serial = Previous(Serial), Peek([Cumulative Sum]), 0) + [Monthly Revenue] AS [Cumulative Sum],
    If(Peek([Cumulative Sum]) < 0 AND If(Serial = Previous(Serial), Peek([Cumulative Sum]), 0) + [Monthly Revenue] > 0, 1, 0) AS [Break Even Flag]
RESIDENT ERP
ORDER BY Serial, Date;

DROP TABLE ERP;

LEFT JOIN (Final)
LOAD Serial,
	[Break Even Date] - [First Date] AS [Break Even Days],
    1 AS [Break Even Flag];
LOAD Serial,
	Min(Date) AS [First Date],
    Max(If([Break Even Flag] = 1, [Date], 0)) AS [Break Even Date]
RESIDENT Final
GROUP BY Serial;

[ElPantera]

Thank you so much! Working perfectly and as the desired outcome. I am able to see each serial(product) time interval to break-even based on the monthly re-occurring income from the initial sale. 

You the best @Nicole-Smith