I need to only select records from this year for the current fiscal month and last year records for the same month.
I'm really new to cognos.
I can't have parameters.
To make it simple I have these 2 files that are link together by Calendar Date
MainFile A CalendarDate, Sales
CalendarFile B CalendarDate, FiscalYear, FiscalMonth
On my MAIN query, I set up an expression to calculate This Fiscal year and last Fiscal year and puts them in a data item. Then create a filter to select FiscalYear containing the Fiscal year data item.
For the month, I cannot just use the current month. I need a fiscal month based on our calendar.
This is my problem.
First I need to query the calendar file to get the fiscal month based on today's date. Is this a new query?
Then on MAIN Query, I can set a filter to select only the fiscal month that is returned from the 2nd query.
But how do I link the current fiscal month from my 2nd query to the Main query when setting up the filter?
Thank you in advance.
sound like you are using relational.
why don't you link (join) your calendar file (this is a database table/query item right?) with your time dimension in your model?
We are only starting to create our tables. We currently don't have time dimension. We just have a separate calendar file.
This is what I did:
I created a second query to select the fiscal month from the calendar file based on todays date.
Then on the main query,
I enabled Cross joins.
Then added a filter to select only if fiscal month = fiscal month from the second query.
This works!
But any issue enabling cross joins or with this approach?
Thank you