topic Re: How can I create a rolling count when a month is selected? in QlikView

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

snehamahaveer — Mon, 07 Sep 2015 15:41:38 GMT

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

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

Gysbert_Wassenaar — Mon, 07 Sep 2015 15:45:34 GMT

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

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

sunny_talwar — Mon, 07 Sep 2015 17:20:54 GMT

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

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

snehamahaveer — Wed, 09 Sep 2015 11:32:16 GMT

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.