Trending on Fiscal year

[#1]

Hi,

I have 3 Fiscal year month

Fiscal Year Periods

Oct FY11 Nov FY 11 Dec FY11 3 month Trend

55 75 85 arrow up or arrow down?? (how to calculated this)

Question 2:

Last Fiscal Month Current Fiscal month

76.7 (compare the current month value to last fiscal month) and to show graphically arrow up or down

Thanks for your help

[pover]

One way you can do this is with set analysis. First, calculate the current fiscal month:

sum({$<[Fiscal Period]={'Dec FY11'} >} Amount)

To do the 3 month trend I assume you ignore the second month and just evaluate if the 3rd month is greater than the 1st month. So,

if(sum({$<[Fiscal Period]={'Dec FY11'} >} Amount)>sum({$<[Fiscal Period]={'Oct FY11'} >} Amount),
'qmem://<bundled>/BuiltIn/arrow_n_g.png', 'qmem://<bundled>/BuiltIn/arrow_d_r.png')

and to compare the past fiscal period it would be something similar.

Of course it would be better to make the fiscal period dynamic in the expression, but I would need to know your date format for Fiscal Period, or is it a string?

Regards.

[#3]

We use 010/2010 , 011/2010 ,012/2010.

We have another column where we have JUL FY10. I suppose we can use either one.

Thanks

[pover]

It would be easier if you have a some date type column to back up the string value. In that way it will be easier to calculate the previous or previous two periods.

In the script, do something like the following:

Load ....
date(date#(right([Fiscal Period],7),'MM/YYYY')) as [Fiscal Date]
From...

Then use Fiscal Date in the following way imagining that the user is going to select a [Fiscal Period]:

{$<[Fiscal Period]=, [Fiscal Date]={'$(=date(max([Fiscal Date])))'}>}

for the current month

{$<[Fiscal Period]=, [Fiscal Date]={'$(=date(addmonths(max([Fiscal Date]),-1)))'}>}

for the previous month

{$<[Fiscal Period]=, [Fiscal Date]={'$(=date(addmonths(max([Fiscal Date]),-2)))'}>}

for 2 months ago

Regards.