I want to add the Supervisor Name to this table. H...
# gd-beginners
d
I want to add the Supervisor Name to this table. However when I do for the few that do not have a supervisor, the numbers stop working. What is the system doing here?
i
Hi Doug, It is bit difficult to guess here, since we are not familiar with your model or with this particular metric and insight, but it is most probably due to the missing/null value. Could you explain a bit how is the “Analyzed” and “Completed %” metric calculated, please?
d
Sorry Missed your message All of these facts come from two tables cases table <- common columns among all tenants client table <- columns only related to one client Analyzed
Copy code
SELECT IFNULL( SELECT COUNT({label/cases.case_interaction_id})
WHERE  {fact/analyze_case_clicked} = 1
AND {fact/eligible} = 1,0)
Completed
Copy code
SELECT COUNT({label/case_interaction_id})
WHERE {fact/ert_completed} = 1 
AND {fact/eligible} = 1
and {fact/analyze_case_clicked} = 1
f
Hi Doug, thanks for the reply! I see that both of these metrics involve the Count of
case_interaction_id
with some filters added via the WHERE function. It seems like adding the Supervisor Name is causing these two metrics to return null values (and since
Analyzed
has an IFNULL, it shows 0 instead). Can you tell me a bit more about the Supervisor Name attribute? How does it connect to the metrics? Is it possible that a null supervisor name impacts some of the objects being used in the metrics, causing them to be null? I would suggest taking the troublesome metrics and simplifying them in steps (you can clone them, to avoid affecting the originals), removing WHERE statements step by step until you find the one that’s interacting badly with Supervisor name.
d
Supervisor name comes from the same communal table called cases. The two tables are Cases and Client cases. They are 1:1 join. meaning for every id in case_interaction_id in cases there is exactly one case_interaction_id in client_cases. However, to solve another issue we set it up as an M:N Join Sometimes a User doesn't have a supervisor, and is null, its when the supervisor is NULL we get these weird things happening. I can't understand what your system is doing here. I already know the issue is the supervisor being NULL, though can't figure out what your system is doing. It should just show NULL.
i
Hi Doug, Thank you for additional details, appreciated. Could you kindly share with me the name of affected workspace and the hostname that you use to access it, please? I’d like to review your model and also the visualization in question. Please feel free to use a direct message if it is more comfortable and secure for you.
f
Hi @Doug Creighton, thanks for the extra details and the patience while we checked it out! After some investigation, I believe I figured out the issue. I checked the data model and noticed that
case_interaction_id
was actually present on several datasets (including the 2 main ones being used here,
cases
and
extra analytics
). The problem was that the COUNT function being used by the metric didn’t set the context precisely, so it didn’t quite know which dataset to use in the calculations. When the
Supervisor Name
attribute was introduced, it seemed to enforce this ambiguity, which would cause your metrics to fail - as it wasn’t clear to the engine how to contextualize the count (i.e.: which dataset it should be using to count the IDs). I suspect the M:N relationship between the datasets also played a part here. The solution was actually quite simple: specifying the dataset to be used in the
Analyzed
metric, like so:
Copy code
SELECT COUNT ({label/cases.case_interaction_id}, {dataset/cases_instacart})
WHERE  {fact/analyze_case_clicked} = 1
AND {fact/eligible} = 1
Once I did that, the visualization worked properly (see the screenshot). I’ve created a test visualization (DM-ing you the details) so you can see it in action. There is also a test metric being used there. Once you’ve adjusted the
Analyzed
metric, feel free to delete both of these test objects!
🙌 1
d
@Francisco Antunes @Ivana Gasparekova, sent you an IM. Looks like this solution will not work. The metric that your using is a communal metrics used by all tenants but you are relating to a table thats individual for the client. Also I want to count the base table and not the joined tenant table.
f
Thank you for letting us know. We will have another look and let you know.
d
When i tested counting with the base table, it gets the wrong result. I know this is likely because of our data model, however, we were forced to do an M:N relationship for a different problem (can't recall what it was) as there wasn't a 1:1 option or something like that