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

    Problem

      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.

        • Re: Problem

          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.