topic Re: Working with Dates and Master Calendar in App Development

Working with Dates and Master Calendar

janruffner — Fri, 02 Apr 2021 08:07:36 GMT

I need to sum the rents of different apartments over time where the tenant is changing and the rent can change as well over time.

I have two dates in one table, a startdate, an enddate and the rent of the apartment. Furthermore I have a master calendar with the column "date". Now I would like to have a graph with "date" on the x-axis and the "sum of the rents" on the Y-axis.

If I use the line chart with the dimension "date" out of a master calendar and as a measure the following expression =sum(if(date > [StartDate] and date < [EndDate] , Rent))

it works. However, the performance is really slow. I would like to solve the problem with a set analysis.

I tried something like this:

=sum( {<date = {'>=$(StartDate)'}, date = {'<$(EndDate)'} >} Rent)

But this does not work. Do you know how to do it?

Re: Working with Dates and Master Calendar

StarinieriG — Fri, 27 Sep 2019 09:52:28 GMT

Hi,

try this expression

=sum( {<date = {">=$(StartDate)<$(EndDate)"} >} Rent)

Re: Working with Dates and Master Calendar

janruffner — Fri, 27 Sep 2019 10:00:15 GMT

Thank you.

But I get a value which does not change over time.

Re: Working with Dates and Master Calendar

marcus_sommer — Fri, 27 Sep 2019 11:40:51 GMT

You need to resolve your start + end date per IntervalMatch to a real date which is then linked to your master-calendar.

- Marcus

Re: Working with Dates and Master Calendar

janruffner — Fri, 27 Sep 2019 15:39:45 GMT

Could you explain how you would do it?

Re: Working with Dates and Master Calendar

marcus_sommer — Mon, 30 Sep 2019 07:07:04 GMT

Just use a few dummy-data and apply the intervalmatch-logic like described in the link and to comprehend the logic and then play a bit with the results.

- Marcus