Filter and calculate sum with two date ranges

Need help App Organization
1

Hello!
Within the multi-tenant app i’m building, I have a data type “Objectives”, and each user within a team has certain objectives (sales, revenue etc). Each objective has a start and end date, resulting in a date range (ex. whole 2023). I added an aimed revenue/day variable as well.
In another page, i would like to display a cumulative sum of all the users within that team but just filtered by the dates picked in this page.

As an example:
User A has 10000$ obj in date range 1/1/2023 - 30/6/2023
User B has 5000$ obj in date range 1/2/2023 - 31/7/2023

In the other page, i would like these users to be able to select two dates in date/time pickers Ex. 1/3/2023 - 1/5/2023 and see what the total objective is for all the users but just for these dates. So basically calculating the date intersection for all users with the filtering parameters and then multiply the days intersected of each user by the daily $ amount they should aim at.

I can’t understand how to do so, can someone please guide me towards the solution? It’s days I try to do it but I don’t really understand how to approach it. Hope I was clear enough, otherwise I can better explain what i need to accomplish.

Thanks so much for the help!!

2

So let’s take your filtering example of March 1st, 2023 - May 1st, 2023.

  • Both User A and User B would be displayed.
  • You know that the number of days you need to calculate is the result of (May 1st, 2023 MINUS March 1st, 2023). 60ish days…
  • You mentioned that each User has a “daily amount” number field.

You should be able to just multiple the “daily amount” of each user by the number of days selected.

Please provide screenshots if you’re still struggling.

3

Yea exactly, conceptually it’s pretty simple, but I don’t understand what I should do, with what element and if I need a workflow as well? Where should I filter, with what formula? I’m real stuck on the “how to start” :confused: I’m not sure what screenshot could help. I dont know where to start in doing so. The Objectives part is all set and working. But how do I now show the single summed number in the other page filtered by dates? What element shall i use? and how do i filter it?

Also, the dates from the users could also not be in the range, say for 2022, these objectives should not be summed and displayed in the final sum of say revenue total.

PS. thanks for helping!!

4

Here’s what I would do:

Demo:

3 Likes
5

Oh my god buddy, you are so kind for taking the time to help me out!! I am flattered and touched.
Tomorrow I will try this out and let you know if I can reach the objective!
But really, thank you so much for helping me, I owe you!
Jacopo

1 Like