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

[sunny_talwar]

Are you using IntervalMatch in your script to link month-year to begin and end?

[tiklabaq]

Hi Sunny,

No, I do not do that because I do not want to generate unnecessary data

[sunny_talwar]

Oh, so the two tables are not linked to each other on anything?

[tiklabaq]

Yes, exactly, I thought we could solve this without intervalmatch?

And suppose I did it with intervalmatch, how do I display each value only once a month or a year?

[sunny_talwar]

1st you don't have to use interval match, but a Cartesian Product on the front end which will use an if statement is not going to give you a good performance if you have a lot of data to work with

2nd you can intervalmatch with the monthstart rather than all dates and this should keep a single value for each month (I believe)

[tiklabaq]

no, there are not so many data, with IF I have also got good results, but if I use as dimension date and not month. Because at months it accumulates the value in the chart for each day and displays the sum of every single day.

[sunny_talwar]

What expression are you using?

[tiklabaq]

sum(if(Date >= begin and Date <= end, total value))

[sunny_talwar]

How about if you do this

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