Extract Month from Date

[t0mato]

Hello Cognoise users,

I'm trying to extract or create a way to create a summary by month, from a given date. The goal is to see the total number of applications, by division, per month.

The items are as follows:

Column A - Division (there are 25 different divisions that are listed in the report) I've grouped the report by Division
Column B - Total Applications (This is calculated by a query that counts the candidate names. Candidate Name is included in the report to be counted to achieve the total number of applications)

So the report looks like:
Column A - All 25 divisions are listed vertically
Column B - A total of applications received for each divsion. Since it is grouped by Division, this total is respective of the division.

I have a variable in my package that is Application Date, though nothing for application month.

If I want the report broken down by division (as it currently is), but have the total applications broken down by month (instead of a total for the date range applied), how can i achieve this? Can it be done via the Date Applied variable?

Thanks!

[bus_pass_man]

Presumably you are working with a relational package, not DMR. Knowing that would be helpful for you and for anyone trying to help you.

The following is assuming that your date is a table separate from the fact table.

Create a group by determinant in the package on the time dimension¹ query subject.

_month will return the number of the month in a date.  Use that as the key to the group by determinant.

You can use that with a simple case expression to get the month name.  Use that as an attribute of the key.   If you were using DB2, they have a monthname function.

There's _year and other similar functions. You can find them in the date section of the functions list in the FM expression editor.  You can use them to build up other determinants to have a hierarchy. 


As it says on the tin, a group by determinant will group by the key when the key or one of its attributes is used in a report.

In addition, you can select your date in report studio and choose insert calculation.  There's a list of default expressions including Month.  It will return the month number.

I'm not quite sure what you mean by variable.  You seem to mean query item.







1. The relational concept, not the DMR thing, which is conceptually built on top of the relational concept.  Surprisingly, not everyone knows that.

[t0mato]

Thank you! This helps a lot!

Presumably you are working with a relational package, not DMR. Knowing that would be helpful for you and for anyone trying to help you.

The following is assuming that your date is a table separate from the fact table.

Create a group by determinant in the package on the time dimension¹ query subject.

_month will return the number of the month in a date.  Use that as the key to the group by determinant.

You can use that with a simple case expression to get the month name.  Use that as an attribute of the key.   If you were using DB2, they have a monthname function.

There's _year and other similar functions. You can find them in the date section of the functions list in the FM expression editor.  You can use them to build up other determinants to have a hierarchy. 


As it says on the tin, a group by determinant will group by the key when the key or one of its attributes is used in a report.

In addition, you can select your date in report studio and choose insert calculation.  There's a list of default expressions including Month.  It will return the month number.

I'm not quite sure what you mean by variable.  You seem to mean query item.







1. The relational concept, not the DMR thing, which is conceptually built on top of the relational concept.  Surprisingly, not everyone knows that.

[Cape Cod Gunny]

Presumably you are working with a relational package, not DMR. Knowing that would be helpful for you and for anyone trying to help you.

What does DMR mean?

[bus_pass_man]

Dimensionally Modelled Relational.