Using an example of a store, selling products to customers:
a single customer buys a single product at a single store every month for 3 months. - If I have a measure file that aggregates at the store level, I assume this will show a customer count of 1 and an order count of 1 for each month and when aggregated to the Quarter level will show as 3 customers and 3 orders. As I would like to have the Quarter aggregation show a single customer with 3 orders - will loading the measure with an additional customer dimension with a customer id produce the appropriate results?

This message has been edited. Last edited by: cbrady,

No, that would not do it, because you'd still be aggregating the measure even for the one customer once you jumped up to the quarterly level.

Let me please suggest to you that the reason this looks like a limitation is you're trying to measure two different things in one measure.

You now have two measures that answer two questions
1. "Which customers are buying in total?"
2. "Which stores have the most buys?"

Now you wnat to answer a different question.

3. "Which customers buy a product consistently?"

So you might want to create a "Customers-to-orders" measure - a ratio of customers to orders - in addition to measuring customers to orders separately. That is a different piece of information derived from the same data. And the ratio processes the data properly and will aggregate it properly.

thanks

Actually, there were several things I wanted to measure. I do want to know that I had 250 distinct customers buying in the month of Jan, 150 customers that bought in Feb and 300 customers that bought in March. Those customers are distinct in that month. Rolled up, I want to know that 325 distinct customers bought in the 1st quarter - if it simply aggregates the count - that will show as 800 customers who bought product. We do measure our "stores" on the number of distinct customers they bring in - regardless of what/how much they buy.

Is there a different way to load the customers such that it would aggregate at a distinct level?

Calculating distinct customers would be done during the load of the measure and is not a post-processing step during aggregation. Think of this the way you would do it in OLAP. You'd have a measure called distinct customer purchases. You would not re-use the measure of total customers to do this.

It's really a different way of thinking. The reason you would separate them is in terms of your strategy, if you want to measure distinct customers it is because you had a corporate strategy to increase the number of different people in the store, as opposed to repeat customers.

In terms of how you'd get the distinct count, please have a look at http://techsupport.informationbuilders.com/mips/40022539.html which has tips on how to generate a distinct count using WebFOCUS. The example uses CAR (as many WebFOCUS examples do) but it's operable for any data.

Bob,

Is there perhaps a different url for that reference? It results in a page not found error(at least for me). I do know how to generate the unique count for the customers for each month, quarter, year - it's a question of getting it into the metric. If I load it at the month level - pmf will aggregate incorectly at the quarter & above levels. Is there a way to load the measure theree times - one for each time dimension level - and not have pmf do the aggregation?

Make it a ratio and divide by 1 (which during load becomes the count). Then you'll get a mean count when you aggregate.

If you only want to "show latest" you have to use PMF 5. PMF 4 doesn't have show latest.

thanks

ahh - the lightbulb moment

thanks Bob!