Calculated Field for Ageing

[Annette1818]

HI 

I am trying to create a calculated field for ageing. I created a field that calculates the Overdue age of an invoice and want to place it into buckets, below is my expression.  I do however get an error .

If([Overdue Age]<=30,'0-30',If([Overdue Age]>= 30 and[Overdue Age]<=60,'31-60',If([Overdue Age]>=61 and [Overdue Age]<=90,'61-90',If([Overdue Age]>=91 and [Overdue Age]<=120,'91-120',If([Overdue Age]>=121,'121+'),'120+'))))

I keep on getting an error of "Missing Right parenthesis)  

Please help

Thanks

Annette

[Sarkunelis]

Hi, 

Does this works?

=If([Overdue Age]<=30, '0-30', If([Overdue Age]>=30 and[Overdue Age]<=60, '31-60', If([Overdue Age]>=61 and [Overdue Age]<=90, '61-90', If([Overdue Age]>=91 and [Overdue Age]<=120, '91-120', If([Overdue Age]>=121,'121+','120+' )))))

[Annette1818]

HI

Thank you for the quick reply, no it does not, still getting error 

See below

[Ezir]

Hi @Annette1818 

You can try this:

Pick(Match(-1, [Overdue Age] <= 30, [Overdue Age] > 30 and [Overdue Age] <= 60, [Overdue Age] > 60 and [Overdue Age] <= 90, [Overdue Age] > 90 and [Overdue Age] <= 120, [Overdue Age] > 120), '0-30','31-60','61-90','91-120', '121+')

Regards

Ezir 

[Gysbert_Wassenaar · Accepted solution]

Try moving the ) behind '121+' to the end of the expression:

If([Overdue Age]<=30
	,'0-30'
	,If([Overdue Age]<=60
		,'31-60'
		,If([Overdue Age]<=90
			,'61-90'
			,If([Overdue Age]<=120
				,'91-120'
				,If([Overdue Age]>=121
					,'121+'
				)
			)
		)
	)
)

And might as well remove tests that are redundant.

[Annette1818]

HI,

Thank you, this worked perfectly

Appreciate it

Annette