Age Bucket values repeated on last bucket

[drubinof]

There are 4 age buckets, see below.  The last bucket (>= 91) always has the same value as the preceding bucket (61-90), even though records do not exist that meet the criteria.  This situation occurs even if another age bucket is created before the last  bucket.  The last bucket always takes the value of the preceding bucket.

if([Service Date]>=today()-30 and[Service Date]<=today(),[Service.Charged Amount],0)

if([Service Date]>=today()-60 and[Service Date]<=today()-31,[Service.Charged Amount],0)

if([Service Date]>=today()-90 and[Service Date]<=today()-61,[Service.Charged Amount],0)

if([Service Date] >= (today()-91),[Service.Charged Amount],0)

[drubinof]

Update: The last bucket is summing across all the preceding buckets.

[chrismarlow · Accepted solution]

Hi,

In order these read;

Amounts between 30 days ago and Today (inclusive)

Amounts between 60 days ago and 31 days ago (incl.)

Amounts between 90 days ago and 61 days ago (incl.)

Then your last one is; 

Amounts less than or equal to 91 days before today

Which if you don't have amounts in the future, will be (give or take amounts that are exactly 91 days) the sum of the other three. If your 4th bucket is supposed to be things older than 90 days I'd go for;

if([Service Date] <= (today()-91),[Service.Charged Amount],0)

Cheers,

Chris.

[drubinof]

LOL oops