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?

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?

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

SELECT IFNULL( SELECT COUNT({label/cases.case_interaction_id})
WHERE  {fact/analyze_case_clicked} = 1
AND {fact/eligible} = 1,0)

Completed

SELECT COUNT({label/case_interaction_id})
WHERE {fact/ert_completed} = 1 
AND {fact/eligible} = 1
and {fact/analyze_case_clicked} = 1

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.

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.

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.

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:

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!

@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.

Thank you for letting us know. We will have another look and let you know.

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

Hello Doug, My name is Jakub and I will be assisting L2 engineer. I am analyzing your data model to understand better did we reach the limits of the functionality or is it something we could try to approach differently to reach your desired result. Please allow me some time to investigate and I will come back to you with my findings.

Ok, sounds good

Hi there Doug, Radek from L2 here! Apologies for the delay on investigating this, it's a fairly involved scenario 🙂 Essentially, I believe that Francisco was correct in his suggestion above, however the difficulty in implementing it stems from the fact that as opposed to decreasing the model complexity going from Parent to Child workspaces, the complexity increases. May I ask what lead you to implementing it this way? It would make sense if most (or all) the Child workspaces didn't require

extra analytics

, but outside of that, including

extra analytics

would simplify things and make Francisco's solution feasible.

We need extra analytics because those are the "extra" columns that are only specific to that client. Its pretty essential to our business. We ran into an issue (can't recall) and the solution was to set it up as an M/N relationship. As a 1:1 relationship was not a feature. This gave use the ability to have a 1:1 type of relationship without setting it up that way

Hi Doug, apologies for the delay! I've been trying to come up with a more usecase-friendly solution, but it appears we've arrived at the same idea separately - since the core of the problem is the empty value for a Supervisor Name, adding a placeholder value for the empty cases does work around the issue. For the actual source of the issue, Francisco's explanation on the ambiguity of which dataset should be used for the metric calculation makes the most sense in the context of your setup.