We hope this Post isn't too confusing. Here goes ...
We have learned from people here how to calculate the number of months (using _make_timestamp [see quote below]) from a Prompt Page selected Month and Year.
THANK you again.
QuoteSelMMYY
_make_timestamp (?p_TCalYr?,?p_TMonth?,1)
LTMonths
_months_between ([SelMMYY],[TimeL])
TimeL
_make_timestamp ([Calendar Year],[Calendar Year Month #],1)
With that knowledge we have coded Data Item running months like R3, R6, ...., R36 [see quote below]
Quote
Case
When [Q_ABC].[LTMonths] between 0 and 2 Then [Q_ABC].[TheHours]
End
Our Question ...
Based on the Prompt Page selected Month and Year, we need to utilize the 'LTMonths' values
to calculate the number of months already occurring in the current Fiscal Year.
The problem, how to take the Prompt Page selected Month to determine the code of the 'between ...' ...
IE, June 2017 is the 9th month of our FY 2017, so a Data Item would be ...
Case
When [Q_ABC].[LTMonths] between 0 and 8 Then [Q_ABC].[TheHours]
End
IE, July 2017 is the 10th month of our FY 2017, so that same Data Item would be ...
Case
When [Q_ABC].[LTMonths] between 0 and 9 Then [Q_ABC].[TheHours]
End
How do we code the 'between x and y' based on the Prompt Page selected month?====================================================
From that knowledge, we would write another Data Item for the Prior FY 2016
IE, June 2017 is the 9th month of our FY 2017, so that Data Item would be ...
Case
When [Q_ABC].[LTMonths] between 9 and 20 Then [Q_ABC].[TheHours]
End
For July 2017 ...
Case
When [Q_ABC].[LTMonths] between 10 and 21 Then [Q_ABC].[TheHours]
End
================================================
We would utilize a maximum of 24 months, IE, September 2017 to calculate ...
For FY 2017 when the selected month is September ...
Case
When [Q_ABC].[LTMonths] between 0 and 11 Then [Q_ABC].[TheHours]
End
For FY 2016 ...
Case
When [Q_ABC].[LTMonths] between 12 and 23 Then [Q_ABC].[TheHours]
End
TIA, Bob
After leaving work to run, realized nested Case should work.
Will try tomorrow and report back if it did.
Corrected ... just added an "and" to "When".
Sample code ...
Case
When extract(month,?p_EndDate?)=4 and [Q_DAW].[LTMonths] between 0 and 6 then [Q_DAW].[Employee and Supervised Contractor Hours]
When extract(month,?p_EndDate?)=5 and [Q_DAW].[LTMonths] between 0 and 7 then [Q_DAW].[Employee and Supervised Contractor Hours]
When extract(month,?p_EndDate?)=6 and [Q_DAW].[LTMonths] between 0 and 8 then [Q_DAW].[Employee and Supervised Contractor Hours]
End
Thanks to all who read this Topic.
I cannot help feeling that some things might perhaps be simplified by some additional attributes in your time dimension, e.g. fiscal periods and months.
I could be wrong as I did not follow completely what you are trying to do, but thought I would mention it.
We were able to utilize code to calculate values for Current and Previous Fiscal Years.
We placed them in a List, and learned that while the values are correct, we cannot get
One Row for each Business Group to display FY17 and FY16 UNLESS there are 2 rows
for each Fiscal Year.
Just converted that List to a Crosstab which presented a new question which we will
make as a new Topic.
My Cognos experience is < 6 months so I may not have developed "best" techniques yet.