Count of records in current month

[Rajat_Ar0ra]

I have loaded the data from an Oracle connection with one of the fields as CREATED_DT which is the format 2020-01-14 22:26:37.617000.I would like to get the count of all records in the current month.

I am trying the following set analysis expression: Count({<CREATED_DT.month={'$(Current_Month)'}>}CREATED_DT)

But I am getting the count of the entire dataset, because I am unable to convert the format of the CRETED_DT field to compare with the current month.

Thanks in advance for your assistance.

[madhuqliklondon]

Hi,

Can you try below if this can converts to date first?

Date(Floor( Date(CREATED_DT)),'DD/MM/YYYY')  As CREATED_DT

[Rajat_Ar0ra]

Hi Madhuqliklondon,

This worked, I have now loaded the CREATED_DT data in theDD/MM/YYYY format. I also loaded a column CREATED_DT_MoNTH with format MM.  Now, I need to count the number of recored with Created_DT in this month.

For that, I have created a variable Current_Month=month(Today()) which gives me "7". Is there a way to count the number records like this: Count({<CREATED_DT_MONTH={Current_Month}>}CONT_ID) where CONT_ID is across the entire column.

Thanks for your assistance.

[madhuqliklondon]

Hi Rajat, sorry i was away from work. did you get to the solution?

[jcdatasax]

You can try a more dynamic formula in set analysis.... perhaps something like this:

Created_DT = {">=$(=Monthstart(MAX(Created_DT)))"}

The issue with the month - only - variable is that you may bring in records from prior years.