Count Number of Non-consecutive Months in Subsummary Report

[lizzie]

Greetings

I have created a subsummary report to assist with product forecasting. I seem to have hit a brick wall.

The subsummary report is based on product code and then "month as number", allowing one to view the purchases related to a product over x specified months. Some products are clearly not purchased on a monthly basis. While one generally takes the number of products purchased and averages those over the number of months being analysed, I would like to be able to flag those products that have been purchased in consecutive months. Thus, if I am looking at data for April - July, those products that are purchased every month can be flagged with a "4" and will consequently receive a higher weighting.

The attached PDF shows mock data. Basically I want to count the number of months in which a product is purchased but count and getsummary functions don't seem to be working in my favour as these take the total number of records summarised into account.

Hope I am making sense!

CountMonths.pdf

[comment]

This is not quite clear. It looks like you want to count the number of sub-groups (months) in a group (product) - but what has consecutive or non-consecutive got to do with it? Obviously, any product that has ALL the possible months was purchased EVERY month, but how is this significant?

Suppose during 2007 we purchased 12 widgets and 12 gadgets. The widgets were purchased 1 each month, while all 12 gadgets were bought in April. In which aspect would widgets warrant a higher weighting?

[lizzie]

The question was posed for two main reasons, both of which have been identified by yourself...

a) merely to see HOW to perform such a calculation

being in the technology industry, forecasting generally looks at a 90 day history before projecting out and yes, one can make use of weighting in the analysis to give more recent data more weight...

Thus, I'm curious to see how one would do it and am not necessarily concerned with the "why"...

I hope this clarifies things?

E

[comment]

The "why" is important: very often a question is asked how to do something complicated, and then it turns out that the REAL purpose can be achieved quite simply. I am still confused regarding YOUR purpose, since giving more weight to more recent data is very different from attaching significance to data being consecutive.

Anyway, to count the sub-groups of a group (if that's the question) you would first define a summary field as count of , e.g. the serial id of the table.

Next, you need a calculation field =

1 / GetSummary ( sCountSerialID ; SubCroup )

And finally, another summary field defined as total of the above calculation field.

Note that if used in a calculation, you must refer to the result as:

GetSummary ( sCountSubgroups ; Group )

It might also be advisable to round the final result, since it can easily fall victim to the vagaries of floating-point math.

Edited July 14, 2008 by Guest