Aging analysis per month

[bumin]

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

[#2]

Hi,

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

Kiran.

[bumin]

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

[#4]

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.

[bumin]

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

[#6 · Accepted solution]

Check this out, using Trellis chart.

Regards,

Kiran.

[bumin]

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

[#8]

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.

GM

[bumin]

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

[#10]

Bumin,

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

Kiran.