topic Aging analysis per month in QlikView

Aging analysis per month

bumin — Sun, 30 Oct 2011 22:24:34 GMT

Hi together,

I want to create an aging analysis

(

open items,
<30 days due,
between 30 and 60 mdays due
more than 60 days

)

for the last rolling 12 months based on a reporting day.

I have created the aging expressions, but I couldn't define the 12 rolling months.

Has anyone did something like this before?

thanks and regards

Bumin

Re: Aging analysis per month

Mon, 31 Oct 2011 05:02:00 GMT

Hi,

Made a few modifications in script for calendar and created a variable to set last 12 monts flag. Check out the attchement.

Kiran.

Aging analysis per month

bumin — Mon, 31 Oct 2011 07:36:58 GMT

Hi Kiran,

thanks for your answer, but I am looking for something different.

I want to see the aging (0, 30, 60)

as per reporting day
as per reporting day - 30 days
as per reporting day - 60 days
...
as per reporting day - 360 days

so the result should be a report with 12 columns (bar chart) showing for this day the aging structure.

Bumin

Re: Aging analysis per month

Mon, 31 Oct 2011 08:22:41 GMT

Hi Bumin,

Just changing from stacked to group in bar is enough. Made this change in the attachment.

It can also be done using calculated dimension. Let me know if the attachment doesnt fulfil your need.

Kiran.

Aging analysis per month

bumin — Mon, 31 Oct 2011 08:29:33 GMT

Hi Kiran,

I would like to have 12 times (for the last 12 months) that what you see in the graph.

I have tried with calculated dimensions, But I cannot access to it in the expression.

thanks

Bumin

Re: Aging analysis per month

Mon, 31 Oct 2011 08:39:00 GMT

Check this out, using Trellis chart.

Regards,

Kiran.

Aging analysis per month

bumin — Mon, 31 Oct 2011 09:17:02 GMT

Hi Kiran,

you don't need the trellis, but the right point was to take the CalenderYearAndMonth as a dimension.

Then you get the aging per month.

thanks and regards

Bumin

Aging analysis per month

Mon, 31 Oct 2011 09:22:01 GMT

Hi Bumin,

If I understood d requirementrightly, it is pretty simple.

you can createone more veriable to store base date, which will store (ReportingDay)- 365 date value.

i.e vBaseDate = (ReportingDay) - 365

Doing this, you havenow basedate available to calculate Ageing bucket..

Here is your new dimensionExpression = class(if([due date]>vBaseDate ,[duedate]-vBaseDate),30)

Instead of classyou can also create nested if bucket to cater you requirement.

Now you can have simpleexpression sum(value) todraw bar chart.

Good Luck.

Re: Aging analysis per month

bumin — Mon, 31 Oct 2011 16:37:59 GMT

Hi Kiran,

I have got the buckets but the result is still not Ok.

for one month 1 get only the documents from the month.

I am looking for all documents in each month.

Example: in October 2010 I have one invoice only (56K)

When I look to the figures of October I want to see all documents from the past and analyze them by aging.

have you an idea, how it should be?

thanks and regards

Bumin

PS: GM, I couldn't get the class working either

Re: Aging analysis per month

Mon, 31 Oct 2011 16:45:57 GMT

Bumin,

You are getting all months but you restrict the bar chart to show only 12 dimensions.

Kiran.

Re: Aging analysis per month

bumin — Mon, 31 Oct 2011 16:53:15 GMT

Kiran,

I want to see only the 12 last months

The report should show the aging as

per last month

per last month -1

per last month -2

...

So each bucket has to show all documents with their aging regarding the period's monthend

regards

Bumin

Re: Aging analysis per month

Mon, 31 Oct 2011 17:03:36 GMT

Bumin,

What I would recommened is in load script create a field called document_ageing which should return Open, <30,<60 ,30-60 etc.

Then you can create this chart with document_ageing, calendar_month and count({<MonthID<={"$(=VL12Months)"}>}document_number). This would be very light and easy to use solution.

Kiran.