Can anyone help me to caliculate number of days?

[durgabhavani]

Hi All,

I am bit new to QV,

I have a payment date field as below.

Payment_Date

01/22/2015

02/15/2015

11/15/2014

09/18/2014

04/14/2015

Firstly, i have scenario to caliculate the number of days based on current date as below?

date(current date) - date(payment_date) as noofdays;

how to convert to date into noofdays?

secondly, i want to display as groups like as below

if noofdays is 10 then "paidbelow 10 days"

if noofdays is 20 then "paidbelow 20 days"

Can anyone help on this?

Thanks,

Durga

[Anonymous]

Try this:

=num(today()-Payment_Date)

Marc.

[mukesh24]

Hi,

1. use networkdays() to calculate number of days --> networkdays ('2007-02-19', '2007-02-25')

   or convert current date & Payment date into number & take difference between 2 dates

2.create bucket in backend

[#4]

You can calculate it by using interval

=if(interval(payment_date-current date,'D')=10,'paidbelow 10 days',

if(interval(payment_date-current date,'D')=20,'paidbelow 20 days')) as Result

If in case of format not equal to dd-mm-yyyy then try like below,

=if(interval(date(payment_date,'dd-mm-yyyy')-date(current date,'dd-mm-yyyy'),'D')=10,'paidbelow 10 days',

if(interval(date(payment_date,'dd-mm-yyyy')-date(current date,'dd-mm-yyyy'),'D')=20,'paidbelow 20 days')) as Result

[durgabhavani]

Thanks for inputs!!

I am trying to implement the conditions on resident table because original table has field "payment_date"  as below

temp:

------

-----

payment_date

-----

----

Overdue:

Load *,

if(condition...) as due_days,                     // i am placing inputs provided from all at the place of if condition //

resident temp;

drop table temp;

but it is showing table not found after giving this condition in resident table and reloading.

Please advice to get output.

Thanks,

Durga

[#6 · Accepted solution]

Check the attached document

use :

=if(Interval(today()-PaymentDate,'DD')<=10,'<10',

if(Interval(today()-PaymentDate,'DD')>10 and Interval(today()-PaymentDate,'DD')<=20,'<20','Others'))

[#7]

In overdue table add a dummy column as 'a' as a and try.

[durgabhavani]

its working, Thanks for every one.

[maxgro]

table:

load *,

today() - Payment_Date as datediff,

Class(today() - Payment_Date, 10, 'days') as datediff2;

load * inline [

Payment_Date

01/22/2015

02/15/2015

11/15/2014

09/18/2014

04/14/2015

];