Summing

[#1]

Morning

I am having a bit of a brain block here, It's the simplest of things to do but my brain has gone. I am trying to sum a field that has 1's in it where the referral date is greater than this time last year. example of my code  =SUM({<Referral_Date = {"MONTHSTART(TODAY()-366)"}>}Referral_Count)  any help will be greatful.

[sunny_talwar]

Try this:

=SUM({<Referral_Date = {">=$(=MonthStart(AddYears(Today(), -1)))"}>}Referral_Count)

[datanibbler · Accepted solution]

Hi,

set_analysis can be tricky. The easiest way to deal with it is - not to use it if you don't have to.

If you just want to sum this field with a condition, just try

>> sum(IF(Referral_Date > Monthstart(Today(), -12), Referral_Count, 0) <<

(The function >> Monthstart(Today(), -12) << returns the start of the same month one year ago.

It is possible with set_analysis, no doubt, but that is resource_heavy and, as one can see (not only from your post, there are many who have trouble with it), error_prone - so avoid it if it's not necessary.

HTH