Rollup summary filtration based on month

[attelli]

i have a MD relationship between Employee(master),submission(Detail).I created a rollup to get the count of submissions made by Employee(master).As it is giving total number of submissions made by employee.my requirement is i need the count of submissions of current month only ...how can i achieve this please help out me...

[phiberoptik]

What dictates a Submission being this month? Created Date? Another date field? What?
(for my example I am going to assume Created Date)

Ok, first you need to create a field on the Submission record to filter the RUS by. Create a formula field on the Submission object where the output type is Text. Use this formula:

IF(AND(
       DATEVALUE(CreatedDate ) >=  DATE(YEAR(TODAY()),MONTH(TODAY()),1),
       DATEVALUE(CreatedDate ) <=  DATE(YEAR(TODAY()),MONTH(TODAY()), 31)
     ),"THIS MONTH", null)

 This will populate "THIS MONTH" in the field if the Created Date of the Submission falls within the current month. If it doesnt, the field will be blank.

Edit your RUS field for Submissions and change the Filter Criteria to: Only records meeting certain criteria should be included in the calculation: FormulaField__c equals THIS MONTH.

Now your RUS field counts only the Submissions related to the Employee record that were created in the current month.

[attelli]

Hi thanks for replying ...

                                      Firstly i created RUS by field on submissions object it is getting saved .when i'm creating a record the record is getting saved but the RUS by field displaying  "ERROR".

                                     Let me clarify i have a master detail relationship between Employee as master and submission as child.my Rollup summary (count) field in employee now giving the no.of submissions made by employees individually. my need is the count of submissions for particular month.i have a Date of submissions field in submissions object..

                                    You told that create RUSby in submissions object but i think it will not visible in employee object to fillter our actual ROLLup.

please  help me out in this regard

[phiberoptik]

You have a RUS field on the Employee object that counts all Submissions related to it right now correct?

You need to follow my directions of creating the formula field on the Submission object.

Then EDIT the RUS field on the Employee object that already exists, and change the Filter Criteria as I instructed in my original post. This will change the value displayed in the RUS from "all Submissions" to "only Submissions this month".

[attelli]

The formula field (RUSby) in submissions object is not visible when i try to change the fillter criteria in employee  this field is not in the list ....

[phiberoptik]

I think i originally said to make the field not visble to anyone, but instead, make it visible to only System Administrator.

I have tested and verified that you can use formula fields as lookup filters for RUS fields, so I know its possible.

[attelli]

Yeah you are absolutely correct  we can  can use formula fields as lookup filters for RUS fields.i getting some other formula fields also in the fillter list but this field (our RUSby ) is not visible.i think the formula is not working fine...bcoz it is displaying “#Error!”  while calculating the value of  formula. please correct the formula so that we can achieve what exactly we want

[attelli]

Hi Aaron thanks for spending time towards me ....i just read that i look up filtration for rollup is only of below data types Currency, Number, Date, Date/Time, Roll-Up Summary

but our formula return type is text ... hope thia is the reason

http://ap1.salesforce.com/help/doc/en/fields_lookup_filters_notes.htm 

[phiberoptik]

If that is the case and your text formula field is not present, then edit it to output type Number and change the IF() results to "1" and "0".

Then make your lookup filter criteria where the RUS field equals 1. The idea is that if the record falls within the current month, the field will contain a "1". Otherwise its a 0.