Dynamic YTD prompt selection

[oscarca]

Hello Cognos gurus,

Does anyone know how to make YTD dynamic based on what month you choose in the prompt. Right now I only have a static filter that works if you want to look at YTD based on current date but I wish to make this dynamic so when I choose for example 2018 March I want to see/display YTD from that month (not from current date) or if I choose 2015 Sep I want to see YTD from that month. So the purpose of this function is to be able to see historic YTDs. The report is using relational data source.

Thanks in advanced!
Best regards,
Oscar

[Cognos_Jan2017]

This may help you get started.

We have 2 prompts for
1 - Month = March, June, Sept, and December
2 - Year

The code below can be modified to include all 12 Months ...
NOTE ... the "From" date begins 2 months back from the
selected date to allow all accounting to be completed.

Case
When ?p_TMonth? = 6 Then 'Feb - Apr FY'+Right(?p_TCalYr?,2)+' Q3'
When ?p_TMonth? = 3 Then 'Nov - Jan FY'+Right(?p_TCalYr?,2)+' Q2'
When ?p_TMonth? = 12 Then 'Aug - Oct FY'+Right(?p_TCalYr?+1,2)+' Q1'
When ?p_TMonth? = 9 Then 'May - Jul FY'+Right(?p_TCalYr?,2)+' Q4'
End

The "Thens" above can be modified, using _make_timestamps, and calculating
Months (back) to calculate Month Totals.  Something like ...
This being for 3 Months back from selected Month ...
Case
When [Query].[LTMonths] between 2 and 4 Then [Query].[TotalL_Pounds]
End

'LTMonths' ...
_months_between ([SelMMYY],[TimeL])

'SelMMYY' ...
_make_timestamp (?p_TCalYr?,?p_TMonth?,1)

'TimeL' ...
_make_timestamp ([Calendar Year],[Calendar Year Month #],1)

HTH, Bob

[Lynn]

Hello Cognos gurus,

Does anyone know how to make YTD dynamic based on what month you choose in the prompt. Right now I only have a static filter that works if you want to look at YTD based on current date but I wish to make this dynamic so when I choose for example 2018 March I want to see/display YTD from that month (not from current date) or if I choose 2015 Sep I want to see YTD from that month. So the purpose of this function is to be able to see historic YTDs. The report is using relational data source.

Thanks in advanced!
Best regards,
Oscar

Are you planning to give the user a calendar control to specify the date to select? If so, this filter will take the year out of the selected date and convert it to January 1st of that year as the start of the date range with the parameter as the end of the date range.

Code Select Expand


[Sales (query)].[Time].[Date] between cast ( cast ( _year ( ?Select Date? ), char(4) ) || '-01-01', date ) and ?Select Date?


[oscarca]

Hey Lynn,

I am planning to give them to option to choose 2018/May format so not Calender date but your solution helped me solve my question.

this was the solution in the end:
cast(year([Date]), VARCHAR(4)) = substring(?pMonth?;1;4) and [Date] <= ?pMonth? + '-31'

Since the parameter ?pMonth? gives me 2015-01 for example, I only needed to add the days '-31' or '-01' depending on if you want to exclude or include the month you start from.

Thanks for great help !
Best regards,
Oscar

[oscarca]

Hey Bob,

Interesting solution, and I will look deeper into this and try some test examples even though Lynns answer gave me the desired result with less code.

Always great appreciation with different solutions.

Many thanks,
//Oscar