Hello all,
I'm having a small issue which I don't know how to solve. I have a table containing our sold Machines and one table with our invoices. I created one fact out of these two. Now I need to calculate the redemption of my machines over time. The table simplified looks like below:
Load *, recno() as ID INLINE [
Start, End, SerialNo, PriceSold
15/01/2011, 15/01/2016, AAAA, 18000
08/08/2011, 08/08/2016, BBBB, 36000
03/04/2012, 03/04/2017, CCCC, 72000
.......
];
Every machine has a 60month redemption period (PriceSold/60 will give me how much the machines redemption is each month).
I want to achieve the total redeption of each machine on a yearly base.
Year 2011: 6600€ (Machine AAAA: 3600, Machine BBBB: 3000)
Year 2012: 21600€(Machine AAAA: 3600, Machine BBBB: 7200, Machine CCCC: 10800€)
Year 2013: 25200€(Machine AAAA: 3600, Machine BBBB: 7200, Machine CCCC: 14400€)
Year 2014: 25200€
Year 2015: 25200€
Year 2016: 19500€(Machine AAAA: 300, Machine BBBB: 4800, Machine CCCC: 14400€)
Year 2017: 3600€ (only machine CCCC is still in this period)
I already created a temp calendar which generated all possible dates in between:
Let vMinDate = num(Date(15/01/2011))
Let vMaxDate = num(Date(03/04/2017))
Calendar:
Load Date($(vMinDate)+RecNo()-1) as Date Autogenerate $(vMaxDate) - $(vMinDate) +1;
FullTable:
Join (Data) IntervalMatch (Date) Load
Start,End
Resident Data;
The above actions don't give me the result I want. Could someone help me out with this one?
If you need more information, or something isn't clear, just ask me.
Thanks in advance all
--- Edit:
I found the solution to my problem. I just needed to do a distinct on the year of the startdate when joining.
