Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Debt level

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.

3 Replies
Gysbert_Wassenaar

See attached example.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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.

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand