Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

datadigger
New Contributor II

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

Re: Debt level

See attached example.


talk is cheap, supply exceeds demand
datadigger
New Contributor II

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.

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.

Then post a correct data sample.


talk is cheap, supply exceeds demand
Community Browser