3 Replies Latest reply: Oct 10, 2013 4:24 AM by Gysbert Wassenaar RSS

    Debt level



      Could anyone please help me with the following problem.

      I want to display 3 lines in the chart (dimension is MyDate):
      1. debt level based on date selection. Chart line should show debt calculated from turnover before min( MyDate ) and then chart should display debt modified by turnover between min( MyDate) and max(MyDate).
      2. invoiced sums
      3. paid sums


      I have trouble calculating 1. Everything I tried either didn't work or had unwanted side effects, for example if i select date range graph #1 stays not limited by my selection @and only graphs #2 and #3 are limited. See attached picture.


      let's say we have the data:

      Type     Date          Amount
      inv      2012-02-15     100$
      pay     2012-06-20     55$

      inv      2013-03-20     80$

      pay     2013-08-01     40$


      If i select year 2013 the chart should have values for 2 dates:
      1. 2013-03-20 should have 125 (100 invoiced minus 55 paid plus 80 invoiced).

      2. 2013-08-01 should be 85  (125-40)


      Thank you for any help.

        • Re: Debt level
          Gysbert Wassenaar

          See attached example.

            • Re: Debt level

              Solution you provided is correct with the exact data i provided
              But it is incorrect in the case when there are multiple invoices or payments on the same day. Script calculates debt level for each entry and then sums them all in my chart. It also works incorrect if I add additional fields like customer code or account and then filter by them.

              I was experimenting with similar scripts at ETL phase but i feel like it's the wrong path. Solution should be smart formula in the expression like this:

              Sum({$<Type={'inv'}>}Amount) - Sum({$<Type={'pay'}>}Amount) + X

              ...where X is the debt level at the start of selected date interval and it should be calculated as [invoiced minus paid] including only dates less than minimum selected.

              Problem is all my attempts to calculate X worked incorrectly or with negative side effects.