Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends
I have in my attached sheet details of invoices issued. I want to prepare a report as shown in the excel itself to obtain the age analysis of customers. Pls some create the QV document attach the same for my reference please.
EDIT: ops, one group is missing (6-10), change it in dimension
Hi Grossi
Thanks for your prompt reply
But I cant see anything in your QV document. It is just blank one. Pls resend
Hi,
You have to create a Age bucket for this and create a dimension for this then you are able to create that report.
Hope this helps
Thanks & Regards
retry
can you see it now?
I have no idea to create age bucket. Pls create one for me and attach.
Thanks
see the attached file
hope this helps
Hi,
Check this script
Temp:
LOAD Date,
InvoiceNo,
Customer,
Amount
FROM
INVOICES.xlsx
(ooxml, embedded labels, table is Sheet2);
LOAD
*,
Today()-Date as diff,
if(Today()-Date = 0, '0',
if(Today()-Date >= 0 and Today()-Date <= 5, '5',
if(Today()-Date >= 6 and Today()-Date <= 10,'6-10',
if(Today()-Date >= 11 and Today()-Date <= 20,'11-20',
if(Today()-Date >= 21 and Today()-Date <= 30,'21-30',
if(Today()-Date >= 30, '> 30')))))) as Ageing
Resident Temp;
DROP Table Temp;
In your load script use the resident table to get aging fields.
Hope this helps
Thanks & Regards
Hi,
You can do this way also see the below code
Temp:
LOAD Date,
InvoiceNo,
Customer,
Amount,
Today()-Date as DateDiff
FROM
INVOICES.xlsx
(ooxml, embedded labels, table is Sheet2);
LOAD
*,
if(DateDiff = 0, '0',
if(DateDiff >= 0 and DateDiff <= 5, '0-5',
if(DateDiff >= 6 and DateDiff <= 10,'6-10',
if(DateDiff >= 11 and DateDiff <= 20,'11-20',
if(DateDiff >= 21 and DateDiff <= 30,'21-30',
if(DateDiff >= 30, '> 30')))))) as Ageing
Resident Temp;
DROP Table Temp;
Hope this helps
Thanks & Regards
Thanks all for the replie