How can I create a rolling count when a month is selected?

[snehamahaveer]

Hi Everybody,

I am trying to count the number of demos my territory managers do for a 3 month period.

I have created a field - Month_Activity_Num which I want to use to create a rolling count. However I tried the rollingcount() function with/without aggregration, set analysis to create a formula which would display the correct value.

It might be my ignorance that is creating problems with the syntax. Can somebody help with this. There might be a simple solution to this query of mine.

The queries I used are:

= rangecount ( above( Count(PRODUCT_FAMILY),0,3) ) 

= Count ( aggr( RangeCount( above( count( {$<MONTH>}  PRODUCT_FAMILY),0,3) ),Month))

= Count ({$<MONTH_ACTIVITY_NUM1={">=7.*"}>}OKM_SALESPARTDESC)

= count ( aggr( rangesum( above( count( {$<MONTH_ACTIVITY_NUM>}  PRODUCT_FAMILY),0,3) ),MONTH_ACTIVITY_NUM))

= count({<Month={'>=$(=only(Month)-2)<=$(=only(Month))'}>}PRODUCT_FAMILY) 

I must use num(MONTH_ACTIVITY_NUM) to create a numeric value for the month value of the related activity.

Thanks in advance

[Gysbert_Wassenaar]

See this document for some solutions: Calculating rolling n-period totals, averages or other aggregations

[sunny_talwar]

Would you be able to share a sample Excel file with your expected output to help you better??

[snehamahaveer]

Hi, Please find attached the file for sample data.

Count(Product_family) gives correct data for the selected months.

I want to be able to create a syntax where it ignores the month dimension and displays data for 3 months.

I use num(Month_activity_number) for calculations using months.

I tried the various syntax using rangecount() and set analysis but the value is not accurate.

If anybody can help with this, it would be very much appreciated.