knockoff Invoices documents as per payment received (Ageing)

[vikasmahajan]

Dear All

Please refer my excel file  APS472-AGE.xlsx  in this excel file in have all documents in first tab in

second tab I have adjusted document and there amounts.

My requirement is arrive ageing analysis  as per adjustments done in adj tab.

Kindly help me to arrive this logic. I have attached PDF file  also as per pdf file I want to develop in qlikview.

Thanks in adv.

Vikas

[Jason_Michaelides]

It's not quite clear what you are asking for.

Are you saying for those docs with a NULL Due_Date you want to get the Due_Date from the Adj sheet for the same doc?

Jason

[vikasmahajan]

Yes Sir , Where due date is there due date will come other wise null. I want to arrive ageing analysis.

Thanks

Vikas

[Jason_Michaelides]

OK.  There are many ways to do this (as there always are in QlikView!) but your simplest method may be to use ApplyMap().  First, load the Adj sheet into a mapping table:

Map_Adj:

MAPPING LOAD

     DOC_NO

     ,DUE_DATE

FROM Adj.xls...;

Then, load the Documents table using ApplyMap() to get the DUE_DATE from Adj.  If the DOC_NO does not exist in the Adj table, then the existing DUE_DATE will be kept.

Documents:

LOAD

     CUSTCODE

     ,DOC_TYPE

     ,DOC_NO

     ,DOC_DATE

     ,ApplyMap('Map_Adj',DOC_NO)     AS     DUE_DATE

     ,DOC_AMT

FROM Documents...;

Hope this helps,

Jason

[vikasmahajan]

Dear Sir,

I want to show Age report Report as per follows

Customer : - APS472     AS ON DATE :31.03.2013

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Doc No          Date                  Doc Amt          adj doc                         adj amt          Os Amt

-----------------------------------------------------------------------------------------------------------------------------------------------------------

INV/7043/4VC  19.11.2011        16                   COL/12-13-VC/0993      16                   Nill

DBN/167/4VC   21.12.2011       500                 COL/12-13-VC/0993      500                 Nill

.

.

INV/12-13-VC/7371                 5000                  COL/12-13-VC/6053    4518                Nil                

                                                                      COL/12-13-VC/7248     216

------------------------------------------------------------------------------------------------------------------------------------------------------------

and So on.

Hope now My requirement is clear now . I am Attaching also revised excel file .

Many thanks for your support.

Vikas

[vikasmahajan]

I have attach sample application sir it is urgent

I want support for arriving the same. As per Attached format of PDF report I want to develop in Qlikview with same output.

Vikas

[Jason_Michaelides]

I'm still not sure how you want your DUE_DATE to be calculated, but how's this.

[vikasmahajan]

Sir I want to calculate ageing amt  due date is valid only for invoice entries I will manage for due date I just want

how to show document amt as distinct  vs adj documents multiple please refer my qvw.

Thanks

Vikas

[Jason_Michaelides]

Vikas,

I'm sorry but I don't understand what you're asking nor what your example says.  In your above post you say you want:

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Doc No          Date                  Doc Amt          adj doc                        adj amt          Os Amt

-----------------------------------------------------------------------------------------------------------------------------------------------------------

INV/7043/4VC  19.11.2011        16                  COL/12-13-VC/0993      16                  Nill

DBN/167/4VC  21.12.2011      500                COL/12-13-VC/0993      500                Nill

.

.

INV/12-13-VC/7371                5000                  COL/12-13-VC/6053    4518                Nil              

                                                                      COL/12-13-VC/7248    216

------------------------------------------------------------------------------------------------------------------------------------------------------------

But how is the Os Amt for INV/12-13-VC/7371 = Nil?  5000-4518-216 = 266.

Jason

[vikasmahajan]

Sir

please refer excel file .