Hi Cognos wizards,
I have been trying to work out how to determine financial period based on Current_date forever → obviously with not much luck.
I need to dynamically determine the current financial period, preferably also financial year, so that my data discrete value slider always sits in current fin month when I run my active report.
In my package I have departure date, Financial month name, financial month number etc. but I am struggling to connect these to current_date
I have tried with different nested if statements as below( My sql is very basic) and I have also tried to join 2 queries where departure Date = current_date. The joining partly works but I can only filter to show JUST current month. I want to show current month AND remaining months in the financial year.
if(Current_date between minimum([Booking Details].[Departure Date Accounting Period].[Departure Date] for[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Short Name]= 'Jan',[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Full Name] = '2017/18') and maximum([Booking Details].[Departure Date Accounting Period].[Departure Date] for
[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Short Name]= 'Jan',[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Full Name] = '2017/18')) then
('Jan')
else if(Current_date between minimum([Booking Details].[Departure Date Accounting Period].[Departure Date] for[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Short Name]= 'Feb',[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Full Name] = '2017/18') and maximum([Booking Details].[Departure Date Accounting Period].[Departure Date] for
[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Short Name]= 'Feb',[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Full Name] = '2017/18')) then
('Feb')
else if(Current_date between minimum([Booking Details].[Departure Date Accounting Period].[Departure Date] for[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Short Name]= 'Mar',[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Full Name] = '2017/18') and maximum([Booking Details].[Departure Date Accounting Period].[Departure Date] for
[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Short Name]= 'Mar',[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Full Name] = '2017/18')) then
('March')
.
.
.
else if(Current_date between minimum([Booking Details].[Departure Date Accounting Period].[Departure Date] for[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Short Name]= 'Dec',[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Full Name] = '2017/18') and maximum([Booking Details].[Departure Date Accounting Period].[Departure Date] for
[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Short Name]= 'Dec',[Booking Details].[Departure Date Accounting Period].[Departure Financial Year Full Name] = '2017/18')) then
('Dec')
Else Null
I am working with relational data in cognos 11.0.6.
I am sure there is an easy way to work this out as I have all the data I need available, I just can't see how...
Thanks in advance for any help.
RESOLUTION:
I finally managed to find a solution - not a great one but a workable one.
By joining a query where departure_date = Current_date and a query with all other measures I need into a 3rd query i can get the results I want.