Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
gcorazza
Contributor
Contributor

Bar chart with sum values

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 IDContract NumberContract startContract endStatus
110000001/01/201901/01/2021

Activated

110000501/01/201531/12/2018Terminated
210002015/06/202031/12/2025Activated
310015025/04/201824/04/2023Activated
310003325/01/201624/04/2018Terminated
410020019/01/201718/01/2027Activated
510050001/01/202031/12/2022Activated

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.

1 Solution

Accepted Solutions
Vegar
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

1 Reply
Vegar
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post