I am in 3.1 with this client.
I am trying to get a parameter to work (numbers 1 to 12), which should work as a fiscal year start.
This works perfectly in a crosstab, if I used a hard-coded number, such as 6.
[CY] is a calendar year
[CY Month Number] is a calendar month number.
=If [CY Month Number] >= 6 Then ([CY] + 1) Else [CY]
If I use a separate query (DP) and try to use in the report - either as a measure, with max() function, or detail object (of [CY Month Number]) I always get #MULTIVALUE error.
This is what it SHOULD look like:
The requirement is to make a custom fiscal year start, which should reset both month numbers and the actual year (!). The months are working fine. I can’t get the year to work.
Things I have tried:
=Max([Month Prompt].[CY Month Number]) + [CY]
Where [Month Prompt].[CY Month Number] is a single numeric value from a different query (DP) from a prompt.
If you have access to the underlying database you could create a view and add it to your Universe.
We had the special need for ISO dates (for example sometimes January 1 is part of last year or December 31 is part of next year) so our view had several columns that were just computations on what was simply a list of dates. Each year we’d have a task that wiped the table and then inserted new rows from Dec 1 of year-1 to Jan 31 of year+1 (so about 427 days) that was done at the end of the final year end processing.
Then we’d just pull those date objects into the reports.
Try using the UserResponse() function to get the user’s FY Start Month selection and use that in your formula instead of the value returned by the query.