 |
|||||
|
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
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 Performance Management Framework (PMF) [SOLVED] aggregation for distinct counts
Focal Point Forums Performance Management Framework (PMF) [SOLVED] aggregation for distinct counts Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] aggregation for distinct counts | Login/Join |
| Silver Member |
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 | ||
|
| Master |
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! | |||
|
| Silver Member |
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 | |||
|
| Master |
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! | |||
|
| Silver Member |
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 | |||
|
| Master |
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! | |||
|
| Silver Member |
ahh - the lightbulb moment thanks Bob! prod: WF 8.1 On Win2012 database: msSQL2012 test: identical to prod | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums Performance Management Framework (PMF) [SOLVED] aggregation for distinct counts
Focal Point Forums Performance Management Framework (PMF) [SOLVED] aggregation for distinct countsCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |