Problem

[#1]

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.

[#2]

I found the mistake I made. When creating my FullTable I only needed my years.

FullTable:

Join (Data) IntervalMatch (Date) Load

distinct(year(Start)),End

Resident Data;

After the reload I was able to calculate my totals the way I wanted.