1 Reply Latest reply: Aug 24, 2012 5:17 AM by Bart De Bie RSS


      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))



      Load Date($(vMinDate)+RecNo()-1) as Date Autogenerate $(vMaxDate) - $(vMinDate) +1;



      Join (Data) IntervalMatch (Date) Load


      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.

        • Re: Problem

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



          Join (Data) IntervalMatch (Date) Load


          Resident Data;


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