Calculation from data base

Need help Database
1

Hello Bubblers,

I hope you’re doing well :slight_smile:

In my Dashboard, I wish to make a calculation that I cannot manage to get working.

In my data base, I have the 2 following fields:

  • The list of worked days, as you can see below. (format: list of dates)

image

  • And the Daily Rate, as you can see below. (format: number)

image

On my dashboard, I want to multiply the count of worked days by my daily rate. But I have a condition, I want the dashboard only to display the data from last month (which means, worked days from last month).

Please see below what I started to do, can anyone help me with the formula? The result I have is 0 whereas it should not be.

image
image1048×526 47.8 KB

Thank you a lot in advance.

Have a wonderful day,

2

You’ll have to first filter down or apply a constraint to the search of days. You can do this by saying “Do a search for days: (Creation date (not sure what field you are referencing) > Current date and time: -1 month”

This way you are only retrieving the past 1 months days from today. (Sept 20-Oct 20)

If you only want last months days and not current date -1 month, you will have to first change the date to 1, then subtract 1 day from that, leaving you on the last day of last month, then change the hours to 23, minutes to 59, and seconds to 59.

You should now have Sept 30 at 11:59 PM

This will be your ceiling for the search, but now you need a floor which if you subtracted a month would result in Sept 1 at 11:59 PM so we would need to change the hours, minutes, and seconds.

The floor would be "Current date and time (change date to 1, change hours to 0, change seconds to 0, change minutes to 0) +months -1

This will result in Sept 1 at 12:00 AM.

Then you can perform your search with those two constraints, The “Creation date” or whatever date you are referencing must be >= your floor AND <= your ceiling.

1 Like
3

Dear Javier,

Thanks a lot for your answer, I confirm this works!

Very clear explanation, thanks a lot and have a nice day.

Kind regards,
Olivier

4

Great, after looking back through it I’m not sure if you need to change the seconds from 59 to 60 but I would just test both, at worst it would be 1 second off.