topic Re: Age Analysis in QlikView

Age Analysis

upaliwije — Sun, 23 Mar 2014 13:23:17 GMT

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.

Re: Age Analysis

maxgro — Sun, 23 Mar 2014 13:50:22 GMT

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

Re: Age Analysis

upaliwije — Sun, 23 Mar 2014 13:58:54 GMT

Hi Grossi

Thanks for your prompt reply

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

Re: Age Analysis

its_anandrjs — Sun, 23 Mar 2014 13:59:26 GMT

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

Re: Re: Age Analysis

maxgro — Sun, 23 Mar 2014 14:03:09 GMT

retry

can you see it now?

Re: Age Analysis

upaliwije — Sun, 23 Mar 2014 14:03:38 GMT

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

Thanks

Re: Age Analysis

SunilChauhan — Sun, 23 Mar 2014 14:21:24 GMT

see the attached file

hope this helps

Re: Re: Age Analysis

its_anandrjs — Sun, 23 Mar 2014 14:39:14 GMT

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

Re: Re: Age Analysis

its_anandrjs — Sun, 23 Mar 2014 14:48:46 GMT

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

Re: Age Analysis

upaliwije — Sun, 23 Mar 2014 15:15:04 GMT

Thanks all for the replie