Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  Performance Management Framework (PMF)    [SOLVED] aggregation for distinct counts

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] aggregation for distinct counts
 Login/Join
 
Silver Member
posted
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,


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
Master
posted Hide Post
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


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

 
Posts: 919 | Registered: March 26, 2003Report This Post
Silver Member
posted Hide Post
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?


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
Master
posted Hide Post
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 Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

 
Posts: 919 | Registered: March 26, 2003Report This Post
Silver Member
posted Hide Post
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?


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
Master
posted Hide Post
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


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

 
Posts: 919 | Registered: March 26, 2003Report This Post
Silver Member
posted Hide Post
ahh - the lightbulb moment

thanks Bob!


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  Performance Management Framework (PMF)    [SOLVED] aggregation for distinct counts

Copyright © 1996-2020 Information Builders