Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Thanks for your response... Working.. !!
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....
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
No still the same problem is there....
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
I found DAYNAME function in QLIKVIEW REF MANUAL and it's working...!!!!
Hi,
Thats good.
Regards,
Kaushik Solanki