How to Create Date Range values taking todays date as 12-jun-1999

[#1]

Hi Community Members,

I have my Ivoice Date(billing date) and want to take current date as 12-jun-1986.I have written the following code ,but I am not getting any result and also any error, please help me out in this issue

If(Num#(Today())-Num#(INVOICE)>=0 and (Num#(Today())-Num#(INVOICE)<=30),'0-30 Days',

If(Num#(Today())-Num#(INVOICE)>=31 and (Num#(Today())-Num#(INVOICE)<=60),'31-60 Days',

If(Num#(Today())-Num#(INVOICE)>=60 and (Num#(Today())-Num#(INVOICE)<=90),'61-90 Days',

If(Num#(Today())-Num#(INVOICE)>=90 and (Num#(Today())-Num#(INVOICE)<=120),'91-120 Days',

If(Num#(Today())-Num#(INVOICE)>=120 and (Num#(Today())-Num#(INVOICE)<=150),'121-150 Days',

If(Num#(Today())-Num#(INVOICE)>=151 ,'Over 150')))))) as ReceivablesAge

FROM

[..\QVd's\txBilling.qvd]

(qvd);

Regards,

Ramesh

[jonathandienst]

Ramesh

I am not sure exactly what you are asking, but you can simplify and enhance your code:

If(Today()-INVOICE <= 30, '0-30 Days',

If(Today()-INVOICE <= 60, '31-60 Days',

If(Today()-INVOICE <= 90, '61-90 Days',

If(Today()-INVOICE <= 120, '91-120 Days',

If(Today()-INVOICE <= 150,'121-150 Days', 'Over 150'))))) as ReceivablesAge

This is correct if the field INVOICE is in the standard date format for your model. If you mean that the INVOICE is in the format of 12-jun-1999, then you can use this:

If(Today()-Date#(INVOICE, 'dd-mmm-yyyy') <= 30, '0-30 Days',

If(Today()-Date#(INVOICE, 'dd-mmm-yyyy') <= 60, '31-60 Days',

If(Today()-Date#(INVOICE, 'dd-mmm-yyyy') <= 90, '61-90 Days',

If(Today()-Date#(INVOICE, 'dd-mmm-yyyy') <= 120, '91-120 Days',

If(Today()-Date#(INVOICE, 'dd-mmm-yyyy') <= 150,'121-150 Days', 'Over 150'))))) as ReceivablesAge

Finally, a better way to do this is with interval matching - see the manual and search here for many examples.

Hope that helps

Jonathan

[#3]

Thanks Jonathan,

If(Today()-txBilling.INVOICE=0,'Current',

If(Today()-txBilling.INVOICE<=30,'0-30 Days',

If(Today()-txBilling.INVOICE<=60,'31-60 Days',

If(Today()-txBilling.INVOICE<=90,'61-90 Days',

If(Today()-txBilling.INVOICE<=120,'91-120 Days',

If(Today()-txBilling.INVOICE<=150,'121-150 Days','Over 150')))))) as ReceivablesAge

I Have tried this but I am getting only  0-30 days values,I am not getting other values.

and I also want to take current date as 12-06-1086 as current date ,how is possible,

please help me out.

my requirement is as follows

RecievablesAge

Current

0-30 Days

31-60 Days

..

over 150 Days

[Miguel_Angel_Baeyens]

Hi,

If you mean you want to use that date instead of Today() (that will change every day you reload), the formula is the same Jonathan shown but using Date('12/06/1986') instead of Today(). For example:

If(Date('12/06/1986')-Date#(INVOICE, 'dd-mmm-yyyy') <= 30, '0-30 Days',
If(Date('12/06/1986')-Date#(INVOICE, 'dd-mmm-yyyy') <= 60, '31-60 Days',

Hope that helps.

Miguel

[#5]

hi Community Members,

I written the code below

If(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')=0,'Current',

If(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=30,'0-30 Days',
If(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=60,'31-60 Days',
If(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=90,'61-90 Days',
If(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=120,'91-120 Days',
If(Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY')<=150,'121-150 Days','Over 150')))))) as ReceivablesAge

My Rquirement is

THe field should look like below

ReceivablesAge

Current

31-60 days

61-90 days

...

Over 150 days

I am getting only  over 150 value in the filed, can please you help me out

[jonathandienst]

Hi

I suggest that you post your application and data (or at least representative samples) that we can look at it in a little more detail. Click "Use advanced editor" above the reply window and use the upload feature there.

Regards

Jonathan

[#7]

I have attached my sample file once check it

Thanks Jonathan

[MayilVahanan]

Hi

Can't able to see the data.. Can you reload once and post it.. Atleast 100 values

[#9]

This is my total file please check it and reply me

[MayilVahanan]

Hi

NUm(Date('03/31/2008')) gives 39538

For minimum Invoice date i.e.9/30/2000 ,Num(Date(AddYears(InvoiceDate,11)) ) gives 40816

so 39538-40816 gives ?

Why you add years with invoice date?

If(Date('03/31/2008')-Date(InvoiceDate)=0,'Current',

If(Date('03/31/2008')-Date(InvoiceDate)<=30,'1-30 Days',

If(Date('03/31/2008')-Date(InvoiceDate,'MM/DD/YY')<=61,'31-60 Days',

If(Date('03/31/2008')-Date(InvoiceDate,'MM/DD/YY')<=91 ,'61-90 Days',

If(Date('03/31/2008')-Date(InvoiceDate,'MM/DD/YY')<=121,'91-120 Days',

If(Date('03/31/2008')-Date(InvoiceDate,'MM/DD/YY')<=151,'Over 150')))))) as ReceivablesAge,

Gives you a result..