# Nested if Statement to create Sub-Fields from my Date Field

Hi There,

I'm a bit stuck on a small problem I have and any suggestions would be much appreciated......

I have a Date field  'Invoice_Date' being used as a dimension in a bar chart.

I would like to group these invoices into 6 catagories Based on their due date, ie. whether the invoice is due at some point in the future or at some point in the past. These catagories would be relative to today 'today()'

The categories would be as follows:

Invoice due in 15 days +

Invoice due in 8-14 days

Invoice due in 0-7 days

Invoice overdue 1-7 days

Invoice overdue 8-14 days

Invoice overdue 15 days +

I gather I need a calculated dimension made of a chain of 'if' statements but cant quite get it to work.

Any suggestions much appreciated   : )

• ###### Re: Nested if Statement to create Sub-Fields from my Date Field

if d is the date field add a calculated dimension

=

if(d<(today()-15), dual('<15',-15),

if(d<(today()-8), dual('<8',-8),

if(d<today(), dual('<0',0),

if(d<(today()+8), dual('<-8',8),

if(d<(today()+15), dual('<-15',15),

dual('other',100)

)))))

• ###### Re: Nested if Statement to create Sub-Fields from my Date Field

Use Class function with a width of 10 days is this is ok

make this as calculated dimension

class( Today(0) - invoiceDate ,7 )

should be the count or sum of invoices

Count(Invoices)   or sum(invoices)

The Class Function will return a data like this

 =Class(Today(0)-InvoiceDate,10) count(Value) 93 160 <= x < 170 3 170 <= x < 180 10 180 <= x < 190 10 190 <= x < 200 8 530 <= x < 540 8 540 <= x < 550 10 550 <= x < 560 10 560 <= x < 570 3 890 <= x < 900 3 900 <= x < 910 10 910 <= x < 920 10 920 <= x < 930 8

in order to make it look better use the replace function to eleiminate <= and < to -

Use replace() function for replacing '<= x <' to -

Replace(Class(Today(0)-Date,10),'<= x <','-')