YTD Date Filter Query

[t0mato]

Hello,

How would one write a query to run a report for a YTD date range? Assume [Date] is the data item to filter.

Thank you!

[t0mato]

Nevermind! This appears to work:

[Date] >= 2021-01-01
AND [Date] <= 2021-12-31

[bus_pass_man]

The between operator could be used too.


Modules have the ability to define relative time measures, which is another approach which you could use in your report.  Two of the relative time measures created out of the box are a YTD measure and current year measure.  The former will filter the measure by the year to date as defined by the as of date parameter.  The latter will filter the measure by the current year as defined by the as of data parameter.  For example, if you set the as of date parameter to be July 31, 2021 then the YTD measure will return all the values from January 1 to July 31 of 2021 and the current year relative time measure would return all the values from January 1 to December 31 of 2021.  Depending on the nature of the data and things like accruals you would use one or the other.  You report would not need to be manually updated.  The next time the as of date is changed and the report is run the report will have the data calculated based on the new as of data value.

[MFGF]

Nevermind! This appears to work:

[Date] >= 2021-01-01
AND [Date] <= 2021-12-31

For something more flexible, you could try

[Date] between _first_of_month (_add_months (current_date, 1 - _month (current_date))) and _last_of_month (_add_months (current_date, 12 - _month (current_date)))

Just a thought...

MF.

[t0mato]

For something more flexible, you could try

[Date] between _first_of_month (_add_months (current_date, 1 - _month (current_date))) and _last_of_month (_add_months (current_date, 12 - _month (current_date)))

Just a thought...

MF.

Thank you! This is helpful. If i'm understanding this correctly, it's setting the date between (1 month - the current month, which would be August) and the last day of the current month (september) - 12 months? I assume it will stop at January to remain in the current year? Can you confirm that this is indeed YTD (1/1/21 - current date)?

[MFGF]

Thank you! This is helpful. If i'm understanding this correctly, it's setting the date between (1 month - the current month, which would be August) and the last day of the current month (september) - 12 months? I assume it will stop at January to remain in the current year? Can you confirm that this is indeed YTD (1/1/21 - current date)?

Hi,

For the start date, it takes the current date, adds (1 - the month from current date) months to it, then finds the first day of the month. If we say today's date is 15th September 2021, adding 1-9 months is actually adding -8 months, so gives 15th January 2021, then returning the first day of the month, which gives 1st January 2021.
For the end date, it takes the current date, adds (12 - the month from current date) months to it, then finds the last day of the month. If we say today's date is 15th September 2021, adding 12-9 months is actually adding 3 months, so gives 15th December 2021, then returning the last day of the month, which gives 31st December 2021.

So it returns 1st Jan in the current year to 31st Dec in the current year.

Cheers!

MF.

[t0mato]

Hi,

For the start date, it takes the current date, adds (1 - the month from current date) months to it, then finds the first day of the month. If we say today's date is 15th September 2021, adding 1-9 months is actually adding -8 months, so gives 15th January 2021, then returning the first day of the month, which gives 1st January 2021.
For the end date, it takes the current date, adds (12 - the month from current date) months to it, then finds the last day of the month. If we say today's date is 15th September 2021, adding 12-9 months is actually adding 3 months, so gives 15th December 2021, then returning the last day of the month, which gives 31st December 2021.

So it returns 1st Jan in the current year to 31st Dec in the current year.

Cheers!

MF.

Great, thanks a lot for the detailed explanation!

[t0mato]

Great, thanks a lot for the detailed explanation!

Actually i'm getting the error that _month is not a recognized built-in function name. Are there any other alternatives?

EDIT: Nevermind, I can use month() instead of _month to get the same result. Thanks!

[t0mato]

The between operator could be used too.


Modules have the ability to define relative time measures, which is another approach which you could use in your report.  Two of the relative time measures created out of the box are a YTD measure and current year measure.  The former will filter the measure by the year to date as defined by the as of date parameter.  The latter will filter the measure by the current year as defined by the as of data parameter.  For example, if you set the as of date parameter to be July 31, 2021 then the YTD measure will return all the values from January 1 to July 31 of 2021 and the current year relative time measure would return all the values from January 1 to December 31 of 2021.  Depending on the nature of the data and things like accruals you would use one or the other.  You report would not need to be manually updated.  The next time the as of date is changed and the report is run the report will have the data calculated based on the new as of data value.

Thanks but i'm not too familiar with modules. How would I go about configuring this?

[MFGF]

Actually i'm getting the error that _month is not a recognized built-in function name. Are there any other alternatives?

EDIT: Nevermind, I can use month() instead of _month to get the same result. Thanks!

Interesting! _month is one of the newer inbuilt functions - what version of Cognos are you using?

The month() function isn't a Cognos function, it is a database function. I generally recommend against mixing inbuilt Cognos functions and database functions in an expression if possible. An alternative Cognos function would be the extract() function, eg extract(month, current_date)

Cheers!

MF.

[t0mato]

Interesting! _month is one of the newer inbuilt functions - what version of Cognos are you using?

The month() function isn't a Cognos function, it is a database function. I generally recommend against mixing inbuilt Cognos functions and database functions in an expression if possible. An alternative Cognos function would be the extract() function, eg extract(month, current_date)

Cheers!

MF.

Thanks for the help as this works perfectly. Let's say for example, I'd like to do a rolling 12 month here that always drops off 1 month. For example, if I run it today (January 17th 2022), it will capture this month and the previous 11 months of 2021 (i.e. it drops of January of 2021 and captures February 2021 - January 2022). If I run it next month, on say February 17th, it will do the same thing and drop off February of 2021 and run through March 2021 - February 2022.

Ultimately, I'm just looking for a rolling 12 month. I thought of changing the first part of the argument from "1 - month(current_date))" to "2 - month(current date))" but I think that doesn't account for the change in years.

Thanks in advance!

Thanks for the help!

[MFGF]

Thanks for the help as this works perfectly. Let's say for example, I'd like to do a rolling 12 month here that always drops off 1 month. For example, if I run it today (January 17th 2022), it will capture this month and the previous 11 months of 2021 (i.e. it drops of January of 2021 and captures February 2021 - January 2022). If I run it next month, on say February 17th, it will do the same thing and drop off February of 2021 and run through March 2021 - February 2022.

Ultimately, I'm just looking for a rolling 12 month. I thought of changing the first part of the argument from "1 - month(current_date))" to "2 - month(current date))" but I think that doesn't account for the change in years.

Thanks in advance!

Thanks for the help!

Hi,

Have you tried:

[Date] between _first_of_month (_add_months (current_date, -11)) and current_date

That would give you everything from 1st February 2021 to the current date in January.

Cheers!

MF.

[t0mato]

Hi,

Have you tried:

[Date] between _first_of_month (_add_months (current_date, -11)) and current_date

That would give you everything from 1st February 2021 to the current date in January.

Cheers!

MF.

Hello!

I actually figured this out and this works perfectly. Thank you!