Question

How to get count of every month between two dates?

need help to generate a report to show records count of every month between two dates. Example as below.. Thanks

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
6 5 12 0 0 6 7 0 0 0 0 0 36

***Moderator Edit-Vidyaranjan: Updated Platform Capability***

Comments

Keep up to date on this post and subscribe to comments

Pega
December 5, 2019 - 2:11pm

Hi,

We can use, DateTimeDifference method for calculating difference in months between two dates.

 

Below is the example we have used in our application (CSHC).

Ex : @(Pega-RULES:DateTime).DateTimeDifference(D1,D2)

 

Thanks,

Abhinav

Pega
December 10, 2019 - 9:45am

Instead, if  you want to do this in a Report Definition, then in the calculation builder, you can reference the function alias Embed-UserFunction.pxDifferenceInMonths. (See screenshot attached) Choose the appropriate one for your database since there are several circumstanced ones. 

 

Pega
December 20, 2019 - 3:18pm

If I understand what you need, you don't want the difference in months between 2 dates, you want a list of the months that fall between 2 dates. I don't believe we have an OOTB function alias (i.e., Embed-UserFunction) that will provide that, and I'm not sure that something in the form of a function alias would be the most useful to you.

At any rate, I suspect that you will need to write your own function alias or possibly a report (to use as a sub-report) to return the list of months or month/year pairs (since you didn't specify whether the start and end dates could be more than a year apart). It will probably involve doing a "SELECT DISTINCT …" and using the SQL built in MONTH(date) and YEAR(date) functions.