Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.