Loading Profile...
How do I add a count metric?
I am testing out using Good Data BI with SugarCRM. I've added three tables from Sugar to GD, the calls, opportunities and employees. I mapped Calls.Assigned_User_ID to the Employee.ID and I also mapped Opportunities.Assigned_User_ID to Employee.ID. For Opportunities GD detected the Amount field was a countable object and added the Sum metric to it, and in calls it did the same for Calls.Duration_Minutes. Now I can build a report that shows me the sum of the amount and the sum of the duration minutes from opportunities and calls broken down by employee user name and sliced by year and quarter. That is something that I can't do in SugarCRM Pro (although I can in Sugar Ent, but it is hard to train a user how to do it). But what I really want to be reporting on is the count of calls, not the sum of the duration in minutes of calls. But GD didn't auto-create any count metrics, so I'm trying to create my own. If I try to create a count metric on a countable field then I get an error when I try to use that field on a report, so instead I've had to create a count field on an attribute, I chose Calls.ID. Now I can build a single dimension report on how many total calls are in the database, but I can't edit the How to do anything useful. The only option I have for How is to use the Call.ID, which then instead of saying there are 50 total calls, it makes 50 rows and has a count of 1 for each row. What I want to know is how many calls each employee made, and once I can do that, I'm hoping to be able to add back the sum of the amount of the opportunities and then subdivide by date (year/quarter).
I'm not sure if I'm adding the count to the wrong field or what my issue is. How do I add a count metric to the calls data that will allow me to report on the count of calls for each employee?
Also, as a separate question... when I did the import, there was only a single date field recognized, but normally there would be more (it recognized the call date, but not the create date or modified date, and I understand why (the time was present)), so if I had the call date as well as the create date, how would GD know which date field to use when I tell the How to use the Quarter of the Year attribute?
I'm not sure if I'm adding the count to the wrong field or what my issue is. How do I add a count metric to the calls data that will allow me to report on the count of calls for each employee?
Also, as a separate question... when I did the import, there was only a single date field recognized, but normally there would be more (it recognized the call date, but not the create date or modified date, and I understand why (the time was present)), so if I had the call date as well as the create date, how would GD know which date field to use when I tell the How to use the Quarter of the Year attribute?
1
person has this question
I have this question, too!
Tell me when someone answers.
The more people who ask this question, the more it gets noticed.
The more people who ask this question, the more it gets noticed.
Create a customer community for your own organization
Plans starting at $19/month
-
Inappropriate?First, thank you for the excellent feedback!
The COUNT metrics are a bit more tricky. We are still scratching our heads how to make them easier to use.
Let me start with a simple visualization of your model. The picture below shows a structure of your data.
The COUNT aggregation function can accept one or two arguments. The first one is what you want to count. You are counting distinct values of an attribute (non-countable field). You choice was right, you used the ID. As the ID is unique for each call, the COUNT(ID) computes the total count of all calls. This number is always the same, no matter in what report you place it. No matter how you filter the calls (e.g. by PHONE # or EMPLOYEE). The COUNT focuses solely on the ID and always returns the same number.
Then you wanted to do something a bit different. Let's say that want to count calls from specific employee. Then you need to explain what you want to count if you put an EMPLOYEE.NAME together with COUNT(ID) to a report. In your case, you want the system to count the number of calls that the employee made. This is the point when you need to use the second COUNT function argument. Our system creates a special "Facts of X" attribute that represents an entity (e.g. calls, opportunities etc.). This is exactly the attribute that you need to use to tell the system, that you want it to count calls when you put COUNT(ID) and EMPLOYEE.NAME in your report. So the final formula is COUNT(ID,Facts of CALL).
You need to use the Advanced metric editor to create such metric. Please click advanced link in the WHAT part of the Slice & Dice and then follow the Custom metric link, give your metric a name (e.g. Count of Calls) and write the metric definition: SELECT COUNT(ID,Facts of CALL) . Please note that you'll need to select the attributes ID and Facts of CALL from the right list (you still can't type the names unfortunately). I would recommend to make this metric global in order you can reuse it in your other reports.
Regarding the mapping of multiple dates in our UI. This is something what we are working on right now. You should be able to do such multiple mappings in our UI very soon.
I would be very much interested in talking to you about your experience with our product. I hope that your feedback can help us to make it far better in terms of usability. Can you please send me an e-mail at zd@gooddata.com? Thanks a lot!
1 person says
this answers the question
-
Inappropriate?Thank you ZD, that worked perfect.
EMPLOYEE
