Skip to main content
Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for 
Search instead for 
Did you mean: 
drubinof
Contributor II
Contributor II

Age Bucket values repeated on last bucket

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)

1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

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.

View solution in original post

3 Replies
drubinof
Contributor II
Contributor II
Author

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

chrismarlow
Specialist II
Specialist II

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
Contributor II
Contributor II
Author

LOL oops