topic knockoff Invoices documents as per payment received (Ageing) in QlikView

knockoff Invoices documents as per payment received (Ageing)

vikasmahajan — Fri, 22 Nov 2013 07:20:42 GMT

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

Re: knockoff Invoices documents as per payment received (Ageing)

Jason_Michaelides — Fri, 22 Nov 2013 07:27:38 GMT

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

Re: knockoff Invoices documents as per payment received (Ageing)

vikasmahajan — Fri, 22 Nov 2013 07:32:54 GMT

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

Thanks

Vikas

Re: knockoff Invoices documents as per payment received (Ageing)

Jason_Michaelides — Fri, 22 Nov 2013 07:40:52 GMT

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

Re: knockoff Invoices documents as per payment received (Ageing)

vikasmahajan — Fri, 22 Nov 2013 09:35:30 GMT

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

Re: knockoff Invoices documents as per payment received (Ageing)

vikasmahajan — Fri, 22 Nov 2013 10:37:14 GMT

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

Re: Re: knockoff Invoices documents as per payment received (Ageing)

Jason_Michaelides — Fri, 22 Nov 2013 10:57:25 GMT

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

Re: Re: knockoff Invoices documents as per payment received (Ageing)

vikasmahajan — Fri, 22 Nov 2013 11:04:28 GMT

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

Re: Re: Re: knockoff Invoices documents as per payment received (Ageing)

Jason_Michaelides — Fri, 22 Nov 2013 11:12:59 GMT

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

Re: Re: Re: knockoff Invoices documents as per payment received (Ageing)

vikasmahajan — Fri, 22 Nov 2013 11:15:20 GMT

Sir

please refer excel file .

Re: Re: Re: knockoff Invoices documents as per payment received (Ageing)

vikasmahajan — Fri, 22 Nov 2013 11:17:51 GMT

This are adjustment i.e. 4518+216 = 5000 adjusted amt and document amt (inv/12-13/7371) is 5000 hence it is nill.

Vikas

Re: Re: Re: knockoff Invoices documents as per payment received (Ageing)

Jason_Michaelides — Fri, 22 Nov 2013 11:19:46 GMT

Errr....

4518 + 216 = 4734, not 5000...!

Re: knockoff Invoices documents as per payment received (Ageing)

vikasmahajan — Fri, 22 Nov 2013 11:26:51 GMT

Sir this is adj status

DocNo	AdjDocNo	adjDocAmt	AdjDocDt	ADJ_ADJDOCADJAMT
COL/12-13-VC/7248	INV/12-13-VC/9042	5000	31/12/2012	4571
COL/12-13-VC/7248	INV/12-13-VC/7371	5000	21/11/2012	216
COL/12-13-VC/7248	DBN/12-13-VC/0293	5000	08/03/2013	213

4571+216+213 = 5000

Re: knockoff Invoices documents as per payment received (Ageing)

vikasmahajan — Fri, 22 Nov 2013 11:28:36 GMT

I have all invoices / credit notes /debit notes and collection in one tab in another tab i have adjusted entry

now I want to calculate o/s means all entry - adj entry = will be my balance (Document wise)

Hope this clear now

Thanks

Vikas

Re: Re: knockoff Invoices documents as per payment received (Ageing)

Jason_Michaelides — Fri, 22 Nov 2013 11:37:45 GMT

Your table above still doesn't make sense but maybe this is what you want?

Re: knockoff Invoices documents as per payment received (Ageing)

vikasmahajan — Fri, 22 Nov 2013 11:47:08 GMT

Many many Thanks Sir for spending your valuable time to resolve my issue

I will check and get back you soon , I have to check with Live data.

Vikas

Re: knockoff Invoices documents as per payment received (Ageing)

vikasmahajan — Fri, 22 Nov 2013 12:03:23 GMT

I really Thankful ! !!!!!!!! You have spend your valuable time to resolve this issue , I will implement in live and get back you.

Vikas