Adding in Date

[#1]

Dear All,

I need to know how to get payment due date based on invoice date and payment terms.

Please check below table.

Invoice NO	Payment Term	Invoice Date
5702021	90 Days Month End	10/05/2011
5702022	60 Days Month End	20/05/2011
5702023	90 Days Month End	25/05/2011
5702024	30 Days Month End	05/06/2011
5702025	30 Days Month End	10/06/2011
5702026	90 Days Month End	18/06/2011
5702027	60 Days Month End	20/07/2011
5702028	60 Days Month End	25/07/2011
5702029	120 Days Month End	28/07/2011
5702030	30 Days Month End	15/08/2011

Let me explain Payment Terms first.

If payment term is 30 Days Month End, due date can be calculated as below.

Invoice Date = 05/01/2011

Due Date = month end of invoice date (i.e. 31/01/2011) + add one full month

So the Due Date = 28/02/2011 (i.e. end of February)

If payment term is 60 Days Month End,

Due Date = month end of invoice date + add two full months and so on.... (for above example : Due Date for 60 Days Month End is 31/03/2011)

Kindly give me an expression to get due date against each invoice number.

[kaushiknsolanki · Accepted solution]

Hi,

    Try this.

    Load

       IN,

       PT,

       ID,

if(left(PT,2) = '30',Addmonths(MonthEnd(ID),1),

if(left(PT,2) = '60',Addmonths(MonthEnd(ID),2),

if(left(PT,2) = '90',Addmonths(MonthEnd(ID),3),

if(left(PT,3) = '120',Addmonths(MonthEnd(ID),4))))) as PDD

     From XYZ.

Regards,

Kaushik Solanki

[#3]

Thanks for your response... Working.. !!

[#4]

Everything is working perfect by while I exported the resulting table into EXCEL file the "DUE DATE" column showing dates as below

01/04/2011

01/05/2011

01/06/2011 etc...

When I am selecting any cell, I found below in formula bar...

31/03/2011  00:00:00

30/04/2011  00:00:00

31/05/2011  00:00:00 etc...

Can you please help me how can I get only date with month end...

31/03/2011

30/04/2011

31/05/2011 etc..

Thansk in advance....

[kaushiknsolanki]

Hi,

    Try this.

Load

       IN,

       PT,

       ID,

Date(if(left(PT,2) = '30',Addmonths(MonthEnd(ID),1),

if(left(PT,2) = '60',Addmonths(MonthEnd(ID),2),

if(left(PT,2) = '90',Addmonths(MonthEnd(ID),3),

if(left(PT,3) = '120',Addmonths(MonthEnd(ID),4)))))) as PDD

     From XYZ.

    Please mark this post as answered.

Regards,

Kaushik Solanki

[#6]

No still the same problem is there....

[kaushiknsolanki]

Hi,

      If its just a Date in Qlikview, then there should be a problem in Excel i guess. 

      Mean while i will look at my side.

Regards,

Kaushik Solanki

[#8]

I found DAYNAME function in QLIKVIEW REF MANUAL and it's working...!!!!

[kaushiknsolanki]

Hi,

    Thats good.

Regards,

Kaushik Solanki