topic Adding in Date in QlikView

Adding in Date

Wed, 17 Aug 2011 09:07:37 GMT

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.

Adding in Date

kaushiknsolanki — Wed, 17 Aug 2011 09:51:59 GMT

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

Adding in Date

Wed, 17 Aug 2011 10:31:59 GMT

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

Adding in Date

Wed, 17 Aug 2011 12:00:41 GMT

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....

Adding in Date

kaushiknsolanki — Wed, 17 Aug 2011 12:12:13 GMT

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

Adding in Date

Wed, 17 Aug 2011 12:16:15 GMT

No still the same problem is there....

Adding in Date

kaushiknsolanki — Wed, 17 Aug 2011 12:23:20 GMT

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

Adding in Date

Wed, 17 Aug 2011 12:32:29 GMT

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

Adding in Date

kaushiknsolanki — Wed, 17 Aug 2011 12:44:22 GMT

Hi,

Thats good.

Regards,

Kaushik Solanki