Age Analysis

[upaliwije]

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.

[maxgro]

EDIT: ops, one group is missing (6-10), change it in dimension

[upaliwije]

Hi Grossi

Thanks for your prompt reply

But I cant see anything in your QV document. It is just blank one. Pls resend

[its_anandrjs]

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

[maxgro]

retry

can you see it now?

[upaliwije]

I have no idea to create age bucket. Pls create one for me and attach.

Thanks

[SunilChauhan · Accepted solution]

see the attached file

hope this helps

[its_anandrjs]

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

[its_anandrjs]

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

[upaliwije]

Thanks all for the replie