Prior Period

[mbrassfi]

I work at a university and am trying to make an automated report that shows the last period from the current period. Our year runs from July to June, and there are 15 periods.  I only want to show from 2-13 (July -June, not the "Fiscal Year Begin" Period 1, or the two audit periods tacked on to the end of the year. 

I have tried this: [FISCAL_YEAR] = extract(year, {SysDate})
                       [FISCAL_PERIOD] = extract(month, {SysDate})

Simply to try and get the current period, but I keep getting the current fiscal year, but the first period, which is "Fiscal Year Start." I think that is because the month in SysDate is not tied with the period number.  Does anyone have a work around?  I can't find anything else on the board about this.

Thanks!

[MFGF]

Hi,

You could set up a query item called CurrentPeriod with an expression along the lines of:

if (extract(month, {SysDate}) = 1) then (8) else
if (extract(month, {SysDate}) = 2) then (9) else
if (extract(month, {SysDate}) = 3) then (10) else
if (extract(month, {SysDate}) = 4) then (11) else
if (extract(month, {SysDate}) = 5) then (12) else
if (extract(month, {SysDate}) = 6) then (13) else
if (extract(month, {SysDate}) = 7) then (2) else
if (extract(month, {SysDate}) = 8) then (3) else
if (extract(month, {SysDate}) = 9) then (4) else
if (extract(month, {SysDate}) = 10) then (5) else
if (extract(month, {SysDate}) = 11) then (6) else
if (extract(month, {SysDate}) = 12) then (7) else
(1)

You can then filter on this:

[FISCAL_PERIOD] = [CurrentPeriod]

Regards,

MF.

[mbrassfi]

So if I gather correctly, the reason that SysDate = 1 is because it is a canned attribute of cognose that pulls on the fiscal year ie

Jan = 1
Feb = 2
Mar = 3

etc

Is this correct?

[MFGF]

Kind of.  Your original example was extracting the month part of Sysdate (today's date), which of course will be 1 this month as it is January.  From the looks of your requirement, you would need this to equate to period 8 for your calendar as January is Period 8.

Is thic correct?

Regards,

MF.

[mbrassfi]

Yah, I got it now, thanks for your help. 

for the prior month i basically copied exactly what you did, except reduced it by one month

if (extract(month, {SysDate}) = 1) then (7) else
if (extract(month, {SysDate}) = 2) then (8) else
if (extract(month, {SysDate}) = 3) then (9) else
if (extract(month, {SysDate}) = 4) then (10) else
if (extract(month, {SysDate}) = 5) then (11) else
if (extract(month, {SysDate}) = 6) then (12) else
if (extract(month, {SysDate}) = 7) then (13) else
if (extract(month, {SysDate}) =  8) then (2) else
if (extract(month, {SysDate}) = 9) then (3) else
if (extract(month, {SysDate}) = 10) then (4) else
if (extract(month, {SysDate}) = 11) then (5) else
(6)

and for the year i did this, which i think works, which will give me the correct fiscal year

if (extract(month, {SysDate}) > 7)
then (extract(year, {SysDate})+1)
else (extract(year, {SysDate}))

[MFGF]

Looks good to me!

MF.

[cschnu]

Yah, I got it now, thanks for your help. 

for the prior month i basically copied exactly what you did, except reduced it by one month

if (extract(month, {SysDate}) = 1) then (7) else
if (extract(month, {SysDate}) = 2) then (8) else
if (extract(month, {SysDate}) = 3) then (9) else
if (extract(month, {SysDate}) = 4) then (10) else
if (extract(month, {SysDate}) = 5) then (11) else
if (extract(month, {SysDate}) = 6) then (12) else
if (extract(month, {SysDate}) = 7) then (13) else
if (extract(month, {SysDate}) =  8) then (2) else
if (extract(month, {SysDate}) = 9) then (3) else
if (extract(month, {SysDate}) = 10) then (4) else
if (extract(month, {SysDate}) = 11) then (5) else
(6)

and for the year i did this, which i think works, which will give me the correct fiscal year

if (extract(month, {SysDate}) > 7)
then (extract(year, {SysDate})+1)
else (extract(year, {SysDate}))

Why not keep it simple like your year calculation?

Code Select Expand

if (extract(month, _add_months(sysdate(), -1))) > 7
THEN (extract(month, _add_months(sysdate(), -5-1)))
ELSE (extract(month, _add_months(sysdate(), +7-1)))


This assumes you are looking for the previous months period, if you want the current period just get rid of the -1s

[mbrassfi]

Cool, I like it.

Thanks for the help