topic Calculation sum depending on the date difference in App Development

Calculation sum depending on the date difference

v_n — Mon, 13 Sep 2021 12:56:08 GMT

Hello everybody,

I am currently working on an application that will show me the total open items of our customers.

For this purpose, there are various time intervals of overdue, e.g. less than 0 days, 1 to 30 days, etc. I calculate this with the due date and a reference date:

= if( Interval([reference_date]-[due_date],'d') <= 0, Sum([Amount]) )

= if( Interval([reference_date]-[due_date],'d') <= 0 and Interval([reference_date]-[due_date],'d') <= 30, Sum([Amount]) )

This works as long as I have the due date as a dimension in my table.

As soon as I remove the due date from the table, nothing is calculated anymore.

How do I make the calculation work in aggregated form?

Re: Calculation sum depending on the date difference

sunny_talwar — Mon, 13 Sep 2021 13:08:12 GMT

Try this may be

=Sum(If(Interval([reference_date]-[due_date], 'd') <= 0, [Amount]))

= Sum(If(Interval([reference_date]-[due_date], 'd') > 0 and Interval([reference_date]-[due_date], 'd') <= 30, [Amount]))

But a more efficient way to do this would be to create a new field in the script

Interval([reference_date]-[due_date], 'd') as duration

and then use that in your set analysis

=Sum({<duration = {"<=0"}>}[Amount])

=Sum({<duration = {">0<=30"}>}[Amount])

Re: Calculation sum depending on the date difference

v_n — Mon, 13 Sep 2021 13:29:03 GMT

Thank you! It works.

The more efficient way does not work for me because I want to select the reference date in a filter within my app.

But the other solution is fine.