Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Sunny,
I've solved it. Really simple
I think this is the best way.
Thank you
Are you using IntervalMatch in your script to link month-year to begin and end?
Hi Sunny,
No, I do not do that because I do not want to generate unnecessary data
Oh, so the two tables are not linked to each other on anything?
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?
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)
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.
What expression are you using?
sum(if(Date >= begin and Date <= end, total value))
How about if you do this
Sum(If(Date >= begin and Date <= end and Date = MonthStart(Date), total value))