How to sum data where the given date is between two other date fields

How to sum data where the given date is between two other date fields

Hello,

I am trying to figure out the combination of query tables and aggregation formulas that will accomplish what I am looking for.  I have a table of events that have a start and end timestamp and a value.  I need a chart that shows a list of dates on the x axis and sums the values of each row if the given date is between the start and end timestamp.  So, if my table looks like this

Start     End     Value
1/1/18   1/3/18   100
1/2/18   1/4/18   200

I would like a chart that shows dates 1/1/18 through 1/4/18 along the x axis with the following values for each:

1/1/18 = 100
1/2/18 = 300
1/3/18 = 300
1/4/18 = 200
1/5/18 = 0 
etc.

I have tried CASE statements, and every aggregation formula I can think of.  I am missing something.  I can sum the values when they start or end, but I can't figure out how to get them to total to date rows that are in between those two.  The only thing I can think of is to create a separate table of all possible dates and do some kind of join, but that sounds forced.  Any ideas?

Thanks,
Tate


      • Sticky Posts

      • How to Add Users to your Organization in ZohoMail?

        A better clarity so you can create other users to start using Zoho Mail. You can directly Add Users from the Control Panel to your Organization. You can invite users with the existing email address. If the person (user) already uses ZohoCRM, then you can import users from Zoho CRM. You can also import them using a .csv file. (if you are planning to add them in Bulk)  In this topic, We will be discussing on how to Add and Invite users only.  The Import options are self explanatory. ____________________________________________________________________________________________________________