Aggr value between begin and end date

[tiklabaq]

Hi Guys,

following issue.

I want to show in a chart the value of contracts. These data are given:

contract	begin	end	total value
123	01.01.2014	31.12.2018	100 000
124	01.01.2015	31.05.2016	500 000
234	10.03.2016	30.11.2019	6 000 000

It's a bar chart with yearmonth as dimension. In this chart I want to see the total value of contracts per month based on begin and end date.

for example:

yearmonth	total value
2015-05	600 000
2016-06	6 100 000
2019-01	6 000 000

So the value should not be cumulated per day or month, rather show only the total value for all contracts which are valid in this period.

Do you have an idea

[tiklabaq]

ok this is good. and how can I make it, if I want to see it in years?

Also, the month is inaccurate if the contract does not start on the 1st, but that's not so bad. if I do that with year, then it does not work. I tested it with yearstart(Date) or yearend.

[sunny_talwar]

May be this

Sum(If(Date >= MonthStart(begin) and Date <= MonthStart(end) and Date = MonthStart(Date), total value))

or this

Sum(If(Date >= MonthStart(begin) and Date <= MonthEnd(end) and Date = MonthStart(Date), total value))

For Year, same logic

Sum(If(Date >= YearStart(begin) and Date <= YearStart(end) and Date = YearStart(Date), total value))

Sum(If(Date >= YearStart(begin) and Date <= YearEnd(end) and Date = YearStart(Date), total value))

[tiklabaq]

it works perfect, but as you said, it is extremely slow

Will it be faster with set analysis?

[sunny_talwar]

Set analysis will not work because the tables are not connected and hence I suggest Interval Match

[tiklabaq]

If you work on it for hours, you lose the overview at some point. Of course I realized that, I think the best solution will be intervalmatch, you're right. And how do I solve this with year and month?

[sunny_talwar]

Well, I would not worry about Year for right now and will focus on Interval Matching on MonthStart field (or MonthStart for Begin and MonthEnd for End)... once you have this, Year can be handled on the front end... can't really imagine how, but once you have Month working and can share you expression... I might be able to tell you how to make it work for year....

[tiklabaq · Accepted solution]

Hi Sunny,

I've solved it. Really simple

1. intervalmatch with date
2. expression: sum(value) / count(distinct date) --> result is the avg per dimension
3. now the chart shows the right values for month, year and so on

I think this is the best way.

Thank you

[sunny_talwar]

I think so too...

I am glad you managed to get an answer... now would you mind closing this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny