Bar chart with sum values

gcorazza — Tue, 28 Jul 2020 20:10:46 GMT

Hi, I have a dataset of all contracts of a company over time. Some contracts are already expired, some will expire in the coming years. What I want is to show a monthly chart with current active contracts and the monthly reduction.

Below are some fake examples of the dataset. I can't attach it here because it's confidential.

Customer ID	Contract Number	Contract start	Contract end	Status
1	100000	01/01/2019	01/01/2021	Activated
1	100005	01/01/2015	31/12/2018	Terminated
2	100020	15/06/2020	31/12/2025	Activated
3	100150	25/04/2018	24/04/2023	Activated
3	100033	25/01/2016	24/04/2018	Terminated
4	100200	19/01/2017	18/01/2027	Activated
5	100500	01/01/2020	31/12/2022	Activated

So this month the chart would show all active contracts, next month it would show all active contracts minus the contracts expiring that month and so on.

I made a chart with the [Contract end] as dimension and I was able to get the contracts expiring each month with:

COUNT({$<[Contract end]={'>=$(=today())'}, Status = {"Activated"}>} DISTINCT[Contract Number])

Also, in a textbox I can get the total active contracts right now:

SUM(COUNT({$<[Contract end]={'>=$(=today())'}, Status={"Activated"}>} TOTAL DISTINCT [Contract Number]))

However, I don't know how to combine these two. I can't use back accumulation because contracts have different durations, so having a fixed backstep wouldn't do the trick.

Note: the filter where Contract end >= today is important because sometimes the database is not properly updated and the contract is shown as activated even if the date is already in the past.

Re: Bar chart with sum values

Vegar — Wed, 29 Jul 2020 04:00:07 GMT

I would recommend you to look into IntervalMatch() . IntervalMatch could help you solve your issue by linking your dataset to a master calendar.

topic Bar chart with sum values in QlikView

Bar chart with sum values

Re: Bar chart with sum values