How do I create a report with a prior year month, YTD and PYTD from 1 file

[jaymoore1756]

I have a file that has transactional data in it. I am trying to create a report with a current month amount, previous year month amount, Current YTD, and previous YTD.
The file has the following columns I am working with
1) Year
2) Period
3) Amount

The current amount field looks like this --
IF ([Year] = ?Accounting Year?) THEN (
IF (?period1? = '1') THEN ([AMOUNT]) ELSE (
IF (?period1? = '2') THEN ([AMOUNT]) ELSE (
IF (?period1? = '3') THEN ([AMOUNT]) ELSE (
IF (?period1? = '4') THEN ([AMOUNT]) ELSE (
IF (?period1? = '5') THEN ([AMOUNT])ELSE (
IF (?period1? = '6') THEN ([AMOUNT]) ELSE (
IF (?period1? = '7') THEN ([AMOUNT]) ELSE (
IF (?period1? = '8') THEN ([AMOUNT]) ELSE (
IF (?period1? = '9') THEN ([AMOUNT]) ELSE (
IF (?period1? = '10') THEN ([AMOUNT]) ELSE (
IF (?period1? = '11') THEN ([AMOUNT]) ELSE (
IF (?period1? = '12') THEN ([AMOUNT]) ELSE (0) )))))))))))

This works the Prior year month looks like this  --

IF ([pyear] = ?Prior Accounting Year?) THEN (
IF (?period1? = '1') THEN ([AMOUNT]) ELSE (
IF (?period1? = '2') THEN ([AMOUNT])ELSE (
IF (?period1? = '3') THEN ([AMOUNT]) ELSE (
IF (?period1? = '4') THEN ([AMOUNT]) ELSE (
IF (?period1? = '5') THEN ([AMOUNT]) ELSE (
IF (?period1? = '6') THEN ([AMOUNT]) ELSE (
IF (?period1? = '7') THEN ([AMOUNT]) ELSE (
IF (?period1? = '8') THEN ([AMOUNT]) ELSE (
IF (?period1? = '9') THEN ([AMOUNT]) ELSE (
IF (?period1? = '10') THEN ([AMOUNT]) ELSE (
IF (?period1? = '11') THEN ([AMOUNT]) ELSE (
IF (?period1? = '12') THEN ([AMOUNT]) ELSE (0) )))))))))))

where pyear is (year-1)
this does not work ... It returns the current year

The Current YTD looks like this --
IF ([Year] = ?Accounting Year?) THEN (
IF (?period1? = '1') THEN ([Period 1]) ELSE (
IF (?period1? = '2') THEN ([Period 1]+[Period 2]) ELSE (
IF (?period1? = '3') THEN ([Period 1]+[Period 2]+[Period 3]) ELSE (
IF (?period1? = '4') THEN ([Period 1]+[Period 2]+[Period 3]+[Period 4]) ELSE (
IF (?period1? = '5') THEN ([Period 1]+ [Period 2]+[Period 3]+[Period 4]+[Period 5]) ELSE (
IF (?period1? = '6') THEN ([Period 1]+ [Period 2]+[Period 3]+[Period 4]+[Period 5]+[Period 6]) ELSE (
IF (?period1? = '7') THEN ([Period 1]+ [Period 2]+[Period 3]+[Period 4]+[Period 5]+[Period 6]+[Period 7]) ELSE (
IF (?period1? = '8') THEN ([Period 1]+ [Period 2]+[Period 3]+[Period 4]+[Period 5]+[Period 6]+[Period 7]+[Period 8]) ELSE (
IF (?period1? = '9') THEN ([Period 1]+ [Period 2]+[Period 3]+[Period 4]+[Period 5]+[Period 6]+[Period 7]+[Period 8]+[Period 9]) ELSE (
IF (?period1? = '10') THEN ([Period 1]+ [Period 2]+[Period 3]+[Period 4]+[Period 5]+[Period 6]+[Period 7]+[Period 8]+[Period 9]+ [Period 10]) ELSE (
IF (?period1? = '11') THEN ([Period 1]+ [Period 2]+[Period 3]+[Period 4]+[Period 5]+[Period 6]+[Period 7]+[Period 8]+[Period 9]+ [Period 10]+ [Period 11]) ELSE (
IF (?period1? = '12') THEN ([Period 1]+ [Period 2]+[Period 3]+[Period 4]+[Period 5]+[Period 6]+[Period 7]+[Period 8]+[Period 9]+ [Period 10]+ [Period 11]+[Period 12]) ELSE (0) )))))))))))
)
ELSE (0)

Where period * = IF (?period1? = '1')+ *  THEN ([AMOUNT]) ELSE (0) and so on
This  does not work.
I need to to do this with PYTD.
If any one can provide some guidance on how to structure these statements, it would be appreciated.

[cognostechie]

Do you have a Date field in the table?

Somebody did not know how to do this right so he/she gave the user prompts to choose Current Year, Prior Year , Current Period etc. There is no need for that.

YTD, PYTD, Current Month etc are best determined with a Date, not with Year and Periods. If you have a Date field in your 'Transactional table' let me know and I will give you a clean calculation to determine all of these.

[jaymoore1756]

Yes there is a date field called Transaction Date

[cognostechie]

YTD -

If
(
extract(year, [Transaction Date]   ) = extract(year, current_date) and
[Transaction Date]   <= _add_days(current_date,0)
)
then ([Amount])
else (0)

Prior YTD

If
(
extract(year, [Transaction Date]   ) = extract(year, current_date) - 1 and
[Transaction Date]  <= _add_months (  current_date , -12)
)
then ([Amount])
else (0)

Current Month

If
(
[Transaction Date]   >= _first_of_month (  _add_months (  current_date,0)) and
[Transaction Date]   <= _last_of_month (  _add_months (  current_date,0))
)
then ([Amount])
else (0)

Same Month Last Year
If
(
[Transaction Date]    >= _first_of_month (  _add_months (  current_date,-12)) and
[Transaction Date]    <= _last_of_month (  _add_months (  current_date,-12))
)
then ([Amount])
else (0)

[jaymoore1756]

Ok Now I have data but I need to filter on a prompt page so I can capture the current month being reviewed -- for example for January i would need to capture transactions from January 1st 2013 thru January 31st 2013 for the current month. Would the prompt be on transaction date ?

[cognostechie]

Not sure what you mean. The Column for Current Month would automatically accumulate the data from 1st Jan to 31st Jan if you run the report in Jan. Currently, the Current Month column would show you the data for Feb 2013.

[bi4u2]

Another option for you would be to have 3 separate queries and then create a union query for the report output. All 3 Queries might have columns called 'Prior Year MTD', 'Current Year MTD' and 'Current Year to Date'. In the Prior Year query you would place the 'Amount' expression in the 'Prior Year MTD' column and the other 2 columns would have '0' as the expression.

[jaymoore1756]

Thanks for the grest responses. I want to make some clarifications. This report will be used by a finance person as part of there GL close. She will run the report for a specific month. for example for Febrayry she would run the report for the period 02/01/13 - 02/28/2013. She would expect to see monthly transactions for Feb 2013, monthly transactions for February 2012, YTD transactions for 2013 ( Jan and Feb ) and YTD transactions for 2012. The code that you gave me works but it does not get me exactly what the user is looking for. Can this be modied to meet the criteria above . thanks

Code from post --

YTD -

If
(
extract(year, [Transaction Date]   ) = extract(year, current_date) and
[Transaction Date]   <= _add_days(current_date,0)
)
then ([Amount])
else (0)

Prior YTD

If
(
extract(year, [Transaction Date]   ) = extract(year, current_date) - 1 and
[Transaction Date]  <= _add_months (  current_date , -12)
)
then ([Amount])
else (0)

Current Month

If
(
[Transaction Date]   >= _first_of_month (  _add_months (  current_date,0)) and
[Transaction Date]   <= _last_of_month (  _add_months (  current_date,0))
)
then ([Amount])
else (0)

Same Month Last Year
If
(
[Transaction Date]    >= _first_of_month (  _add_months (  current_date,-12)) and
[Transaction Date]    <= _last_of_month (  _add_months (  current_date,-12))
)
then ([Amount])
else (0)

[cognostechie]

In that case, create a Date Prompt , let's say the name of the Prompt is pDate  If the user wants to run the report for Feb 2013 then they would select the last day of Feb in the Prompt (2/28/2013). If they want to run the report for March 2013 they would select 3/31/2013 from the Prompt.

This will be the code:

Current Month:

If
(
[Transaction Date]   >= _first_of_month (  _add_months (?pDate?  ,0)) and
[Transaction Date]   <= _last_of_month (  _add_months ( ?pDate? ,0))
)
then ([Amount])
else (0)


Same Month Last Year:

If
(
[Transaction Date]    >= _first_of_month (  _add_months ( ?pDate? ,-12)) and
[Transaction Date]    <= _last_of_month (  _add_months (?pDate?  ,-12))
)
then ([Amount])
else (0)

YTD:

If
(
extract(year,[Transaction Date]    ) = extract(year,?pDate? ) and
[Transaction Date]  <= _add_days(?pDate?,0)
)
then ([Amount])
else (0)

Prior YTD:

If
(
extract(year,[Transaction Date]    ) = extract(year,?pDate? ) -1 and
[Transaction Date]  <= _add_months(?pDate?,-12)
)
then ([Amount])
else (0)

[jaymoore1756]

The current month and prior year month look good but the YTD and Prior YTD do not . The company is on a fiscal year not calendar year basis. So for example when I select January  ( which is period 3) I should get current month equal to January ( it does ). Prior year month equal to prior year January (it does). YTD and Prior YTD sould be equal October, November, December and January. It does not - it is only pulling January? Can the code be adjusted for this? Thanks

[cognostechie]

When you post a question here, you should describe your scenario and requirements clearly so as not to mislead people who are investing their time to help you. How can anybody imagine that your requirement is to run the report by Fiscal year ? Even now, you did not mention the beginning date of your Fiscal year !

Secondly, you should try to understand the solution given to you. If you understand the code, you would be able to adjust it yourself to work with your Fiscal year. You can create a data item which will resolve to the beginning date of your Fiscal year and then compare the Transaction Date with that.

Ex (for YTD): Transaction Date >= _add_days(Starting Date,0) and <= _last_of_month (  _add_months ( ?pDate? ,0))

You should also have fields in the Calendar Dimension for Fiscal year, Fiscal Month etc. That would be another way to do this.

[jaymoore1756]

First of all my apologies for not describing clearly the scenariaos and requirements. There was no intent to mislead any one at any point. I make every effort to understand the code and utilize it. Dates in Cognos are not my strong point. I also try to resolve issues my self prior to posting. I have worked on this issue for some time before posting for suggestions. I am not looking for any free rides. I took the example you gave me and modified it and I have a working YTD. Still working on a PYTD. The first day of the fiscal year is 10/01/20xx.

Thanks for your help it is always much appreciated.

[jaymoore1756]

Issue resolved thanks !!!!!!  8)

[cognostechie]

Glad to hear that !  :)

Can you post your solution of Prior YTD so that others can benefit from it ?

[jaymoore1756]

Previous Year to Date
If
(
[Transaction Date]>= _add_days(?Ydate?,-365) and [Transaction Date]<=_last_of_month(  _add_months ( ?Fdate? ,-12))
)
then ([Amount])
else (0)

[jaymoore1756]

Total Solution
Solution:

Prompt Page:

Location
Fdate
Ydate

Report Information:

Parameters:

Fdate = Use the last day of the month. This will capture Current month and Prior Month transaction data, and will also be used to create a date range for the YTD and PYTD data.
Ydate = Select the first day of the fiscal year you want to run. This date combined with the Fdate will create a range that you can capture multiple period tranactions.


Columns Selected From File

GL Account information, Description,transaction status, transaction date, GL period,

Data Items:

Current Month

If
(
[Transaction Date]   >= _first_of_month (  _add_months (?Fdate?  ,0)) and
[Transaction Date]   <= _last_of_month (  _add_months ( ?Fdate? ,0))
)
then ([Amount])
else (0)

Prior Year Month
If
(
[Transaction Date]    >= _first_of_month (  _add_months ( ?Fdate? ,-12)) and
[Transaction Date]    <= _last_of_month (  _add_months (?Fdate?  ,-12))
)
then ([Amount])
else (0)

Current Year to Date
If
(
[Transaction Date]>= _add_days(?Ydate?,0) and [Transaction Date]<=_last_of_month(  _add_months ( ?Fdate? ,0))
)
then ([Amount])
else (0)

Previous Year to Date
If
(
[Transaction Date]>= _add_days(?Ydate?,-365) and [Transaction Date]<=_last_of_month(  _add_months ( ?Fdate? ,-12))
)
then ([Amount])
else (0)

[cognostechie]

Previous Year to Date
If
(
[Transaction Date]>= _add_days(?Ydate?,-365) and [Transaction Date]<=_last_of_month(  _add_months ( ?Fdate? ,-12))
)
then ([Amount])
else (0)

That will miss out 1 days data because of the leap year. If the user chooses Oct 1, 2012 as the starting date of your Fiscal year, then your calculation resolves to Oct 2nd, 2011 as the starting date of the Last Year's Fiscal year.

This will be more accurate:

If
(
[Transaction Date]>= _add_months(_add_days(?Ydate?,0),-12) and [Transaction Date]<=_last_of_month(  _add_months ( ?Fdate? ,-12))
)
then ([Amount])
else (0)

[jaymoore1756]

Thanks I have updated