Confusion about calculated fields

[Matt Jones3]

I created a two calculated fields on the Incident table, "Created Month" and "Created Year".  All they do is return the formatted Month "MMM" or Year "YYYY" based on the sys_created_on table.  The purpose of these fields is to be able to create some pivot tables to show counts by Service and Month.  However, the reports are showing that there are a number of incidents with an (empty) Created Month and/or Created Year.  If I go to the record and view it, it's showing a value there.  I created a simple report just showing all incidents with Created Month and Created Year, and scrolling through the report, I can see that every record is showing a Created Month and Created Year.  However, I can filter by "Created Month" is empty...and get back a bunch of records...even though the report has them filled in.

What is going on here...

[Swapna Abburi · Accepted solution]

@Matt Jones3 

Until you save the record, the calculated field value won't get saved to database. Please save a record and try.

Also, calculated fields are not so popular as they cause performance issues in the long run, So, I suggest to use before/after insert Business rule for this kind of requirements. 

[Matt Jones3]

Honestly, what would be great would be functions field supporting this kind of functionality...   I just want to be able to create a pivot table that groups things by the month or year they were created.  I originally started down the business rule route, but thought that the calculated field would be more backwards compatible....  Silly me.