Sum between two dates

[samvile18]

Good morning all,

Can someone please help me with some syntax?

I have two fields named StartDate and EndDate and I need to sum the total amount of Value that falls between them.

I'm getting a little stuck with the synta, any help is greatly appreciated.

[jvitantonio]

Correcting

sum( {$<StartDate = {“>10/02/2012”}, EndDate = {“<10/15/2012”} >} Sales )

[samvile18]

Thanks for that...I'm assuming I can use the min and max functions instead of stating an actual date?

[jvitantonio]

Of course.You can adapt it to your situation. If you have any further problemas, post your app. so we work on it. Good luck!

[CELAMBARASAN · Accepted solution]

Hi,

     Try with this if your comparing to another field based on the start and end date fields.

Sum( {$<DateField = {“>=$(=Min(StartDate))<=$(=Max(EndDate))”} >} Sales )

Comparing within the same fields means

Sum( {$<StartDate= {“>=$(=Min(StartDate))”}, EndDate= {“<=$(=Max(EndDate))”} >} Sales )

Celambarasan

[samvile18]

This is what I have so far and it seems to be working...

...looking at it actually it seems to be pretty much exactly the same as Celambarasan's above!!

Sum({<ExtractDate={">=$(=Min(CampaignStartDate))<=$(Max(CampaignEndDate))"}>}Value)

[CELAMBARASAN]

Hi,

     If StartDate and EndDate field also linked with the table then exclude the selection of it.

     Check with this

Sum({<ExtractDate={">=$(=Min(CampaignStartDate))<=$(=Max(CampaignEndDate))"},

     CampaignStartDate=,CampaignEndDate=>}Value)

Hope it helps

Celambarasan

[Anonymous]

how do you do it if u want the date to be dynamic. for example you want the data from today to 50 days prior.

when u check tomorrow it should show values 50 days prior to the respective day.

can u pls help me out on this

[Ahmed_Hisham]

Hi I am using the below equation

=sum({$ <[Month Date.autoCalendar.Date]={">=1/1/2021<=31/7/2021"}>} [Qty])

It only returns values for 31/7/2021

Any advice