Data Items Based on TimeStamps

[Cognos_Jan2017]

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.

SelMMYY                           
_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]

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

[Cognos_Jan2017]

After leaving work to run, realized nested Case should work.

Will try tomorrow and report back if it did.

[Cognos_Jan2017]

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.

[bdbits]

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.

[Cognos_Jan2017]

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.