topic Re: Creating Date Range Values in QlikView

Creating Date Range Values

Mon, 17 Sep 2012 16:49:10 GMT

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

Re: Creating Date Range Values

MayilVahanan — Mon, 17 Sep 2012 16:58:07 GMT

Use like this

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

And Check Maximum and minimum date for txtBilling.Invoice value

Re: Creating Date Range Values

Mon, 17 Sep 2012 17:37:26 GMT

I am not getting the values,

Please help me out Ramasamy....how to check the min and max dates,can you write the solution in detail

Re: Creating Date Range Values

MayilVahanan — Mon, 17 Sep 2012 18:00:36 GMT

Use Min(txBilling.Invoice) and Max(txtBiling.Invoice) to know it..

Use Date insteadof Date# , if your txtBilling.Invoice is datefield means

Re: Creating Date Range Values

Mon, 17 Sep 2012 18:57:01 GMT

Hi ramasamy,

the minimum invoice date is 2033th Year and max invoice date is 28459th Year,

and I am getting the same result, can you say where is the problem

Re: Creating Date Range Values

MayilVahanan — Mon, 17 Sep 2012 19:47:53 GMT

Can you post a sample file

Re: Creating Date Range Values

Mon, 17 Sep 2012 23:03:12 GMT

Hi samy,

I have send the file to your mail, please check it and reply me

Regards

Ramesh

Re: Creating Date Range Values

sivarajs — Tue, 18 Sep 2012 05:10:11 GMT

Please check your date format and ensure Date('06/12/1999')-Date#(txBilling.INVOICE,'MM-DD-YY') giving days count

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