Date Conditional Sum

[#1]

Hi all,

I am trying to set up a conditional expression that fulfills the following requirements:

-If today is a Monday, it will automatically sum up the total for the period Friday, Saturday and Sunday preceding.

-It is independent of all selections therefore {1} applies.

-If today is not a Monday, then it will just show the total for the previous day only.

The conditional expression that I came up with is as follows:

if((Today=('Mon')),

  sum({1<Date={'>=$(=Only(Today())-3)<$(=Only(Today()))'}>}NoA),

  sum({1<Date={'$(=Date(Today()-1))'}>}NoA))

However, when the day is a Monday (i.e. today) this is still just calculating the sum for the day before, can someone please advise what is wrong with my expression?

Note that NoA is the fieldname of what I am trying to add up.

Thanks in advance for your kind help.

Armend

[Anonymous]

hi use

=if(weekday(Today())='Mon', sum({1<Date={'>=$(=Only(Today())-3)<$(=Only(Today()))'}>}NoA),sum({1<Date={'$(=Date(Today()-1))'}>}NoA)))

Thanks

BKC

[Anonymous]

ur if statement is wrong

if((Today=('Mon'))

you need to use

if(weekday(Today())='Mon',

Thanks

BKC

[Gabriel]

Hi,

What I can see there is, in your SA, because you want to search your expression should be in double quote, I think your current expression is using single quote. And your weekday function as well needs changing to this

=If((WeekDay(Today()) ='Mon',

sum({1<Date={“>=$(=(Today())-3)<$(=(Today()))”}>}NoA),

  sum({1<Date={“$(=Date(Today()-1))”}>}NoA))

[#5]

Hi BKC,

Thank you for your response, however this is how my expression looked originally and what it does is that it calculates the total to date i.e. not just for the preceding three days but a sum of all records. I need it to add only the preceding three days if today's date is a Monday.

Any further advice would be greatly appreciated.

Thanks,

Armend

[Anonymous]

=If((WeekDay(Today()) ='Mon',

sum({1<Date={“>=$(=(Today())-3)<$(=(Today()))”}>}NoA),

  sum({1<Date = {"<=$(=Today()) – 1)"}>}NoA))

[#7 · Accepted solution]

Thanks BKC,

I managed to figure out where my error was, please see amended statement which now works fine.

=if(WeekDay(Today())='Mon',

  sum({1<Date={'>=$(=Date(Today()-3))<$(=Date(Today()))'}>}NoA),

  sum({1<Date={'$(=Date(Today()-1))'}>}NoA)

  )

Thank you for your assistance.

Regards,

Armend

[Anonymous]

Good !!