Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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.
See attached example.
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.
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.
Then post a correct data sample.