https://www.gooddata.com/ logo
Join Slack
Powered by
Having Issue Counting Local_user_ids for one workp...
# gooddata-cloud
d
Having Issue Counting Local_user_ids for one workplace Here:https://reporting.laivly.cloud/dashboards/#/workspace/tenant-sephora-v3/dashboard/b1c1ffdb-88ea-47e7-9593-05d9688f9462 This is on the above workspace dashboard: any date will work but for this example I chose May 7th 2025 I made a histogram and its overcounting. For example the None is supposed to be 5 as shown when you add local_user_id but when I remove it its 8. The same can be said for the others. The total count of Local_user_ids is 51 but in the next image it says 90 Any ideas?
y
Hi Doug, thanks for your patience. I used impersonation on the report you shared, to investigate the issue further. I was unable to reproduce this issue. I created a table using the same metrics and filters in place. I also tried creating a histogram (column chart) using the same, and the values in the table were matching the values displayed in the column chart. Are you able to share more information to enable us reproduce this issue. Also, when creating the histogram, is anything being changed or is it the same metrics and filters being applied?
d
every time I do the exact same thing I get what the picture shows. also in this dashboard is where I see the discrepancy and so does our customer
For example here is the image of them side by side on a dashboard. one is 5 the other is 8. I don't understand how you can't recreate this. Everyone else that goes to it and tries gets the wrong number. The only filter I used is date so we can see the same number which you can see in the image @Yvonne Changamuka
j
Hi Doug, sorry for the delay in this. This most recent screen shot really helps point out the issue. I've created this testing report to break everything down: https://reporting.laivly.cloud/analyze/#/tenant-sephora-v3/943cec68-6242-468c-a4e5-27e433ee66e3/edit From this, it appears that the total should be 5. Based on this report, it seems there are a lot of empty and missing values. However, the overcounting happens because the 
TEST-NONE
metric is counting 
Userids id
where the “Utilized % - Spark Case Notes” equals 0. This utilization metric is grouped by user ID, but when you remove 
local_user_id
from the visualization, the grouping level changes. That causes the count to operate over a broader grain — essentially counting multiple rows for the same user if they appear more than once in the dataset. So even if there are only 51 unique users, the count jumps when the system no longer knows to group by that ID. GoodData MAQL doesn’t support 
COUNT(DISTINCT)
directly, so to get accurate numbers, you need to make sure the grouping stays intact. One way to fix this is to rewrite the metric like this:
Copy code
SELECT COUNT(Userids id)
BY Userids id
WHERE Utilized % - Spark Case Notes = 0
This way, each user is only counted once, regardless of how many rows they have. You can also include 
Userids id
in the insight and just hide the column — this forces the correct grouping without affecting the visual layout. Basically, you will want to lock the granularity by the primary key. Could you give this a try and let us know how you get on?
d
I tried Usersids id already and i got the same result. Per a workspace local_user_id is unique so that made sense
The test - none is by Userids id
Sorry wrong image
j
Hi Doug, i'm sorry, did you change the metric definition at all? If so, how is it defined?
d
Copy code
SELECT count({label/dim_sidd_user_ids.userids_id})
WHERE(
SELECT {metric/utilized_-_spark_case_notes}
BY {label/dim_sidd_user_ids.userids_id}
) = 0
thats the Test - None. I had tried to already swap out local_user_id with users_id And the above space you can change or test whatever you want - its private internal board
j
can you try making the count using the by clause rather than in the second WHERE statement?
SELECT COUNT(Userids id) BY Userids id
d
I don't understand what your asking here There is only one where statement.
This?
Copy code
SELECT count({label/dim_sidd_user_ids.userids_id})
BY {label/dim_sidd_user_ids.userids_id}
WHERE(
SELECT {metric/utilized_-_spark_case_notes}
BY {label/dim_sidd_user_ids.userids_id}
) = 0
I don't really get it because the below would just be 1
Copy code
SELECT count({label/dim_sidd_user_ids.userids_id})
BY {label/dim_sidd_user_ids.userids_id}
Sorry I missed this, I will try now
Copy code
SELECT COUNT(Userids id)
BY Userids id
WHERE Utilized % - Spark Case Notes = 0
I tried what you said above @Joseph Heun and its even more wrong
@Joseph Heun any other thoughts?
j
Still trying to find exactly how the 8 is being counted. We have a testing report created here showing the breakdown: https://reporting.laivly.cloud/analyze/#/tenant-sephora-v3/943cec68-6242-468c-a4e5-27e433ee66e3/edit For the time being is it possible to just add the attribute to the bar chart to get them to display correctly?
d
I don't know what you mean, which attribute?
I don't know if this helps but if I did Tenant reporting base V3. Which is the parent space that includes each workspace - the numbers do match. Most workspaces it does match but not this one and a couple others it does
j
User id. It is displaying the correct number, 5, when it is placed in the report, is that correct?
Ok. Are any wdf applied?
d
The bar chart, i can't make it equal the right number. The table yes.
I can't add user_id to the bar chart it would look crazy
Not sure i understand the question each workspace has 1 wdf filter. We have many workspaces all under the Tenant reporting base v3
So the link i sent you has a wdf filter applied yes.
Also, the other thing I notice is that some dates the data matches and other dates the data does not match
The other thing I noticed was the SQL query When I add the date filter it goes crazy long without date filter
Copy code
SELECT CASE WHEN `def_m_7768b660d2e0dc3e96dff8ca0d2c7d1f` THEN `m_7768b660d2e0dc3e96dff8ca0d2c7d1f` ELSE NULL END `m_1`, CASE WHEN `def_m_3f241f772f07528ad84b833f6e074664` THEN `m_3f241f772f07528ad84b833f6e074664` ELSE NULL END `m_2`, CASE WHEN `def_m_f4920fd4d062a82b008b212aaf171d29` THEN `m_f4920fd4d062a82b008b212aaf171d29` ELSE NULL END `m_3`, CASE WHEN `def_m_dbc5d9ac578be404c12420fcd3baaacd` THEN `m_dbc5d9ac578be404c12420fcd3baaacd` ELSE NULL END `m_4`
FROM (SELECT COUNT(CASE WHEN COALESCE(`t7`.`def_m_9e925cbf1091294fd907261015c6ad8a`, FALSE) AND COALESCE(`t7`.`def_m_5cce2edad1fe49c3be3c82cc4d97107f`, FALSE) AND COALESCE(CAST((`t7`.`m_9e925cbf1091294fd907261015c6ad8a`) AS DOUBLE) / NULLIF(`t7`.`m_5cce2edad1fe49c3be3c82cc4d97107f`, 0), 0.0) = 0 THEN `t2`.`a_label_dim_sidd_user_ids_local_user_id_tenant__1ba7abb141a9ba` ELSE NULL END) `m_7768b660d2e0dc3e96dff8ca0d2c7d1f`, COUNT(CASE WHEN COALESCE(`t7`.`def_m_9e925cbf1091294fd907261015c6ad8a`, FALSE) AND COALESCE(`t7`.`def_m_5cce2edad1fe49c3be3c82cc4d97107f`, FALSE) AND 0 < COALESCE(CAST((`t7`.`m_9e925cbf1091294fd907261015c6ad8a`) AS DOUBLE) / NULLIF(`t7`.`m_5cce2edad1fe49c3be3c82cc4d97107f`, 0), 0.0) AND COALESCE(CAST((`t7`.`m_9e925cbf1091294fd907261015c6ad8a`) AS DOUBLE) / NULLIF(`t7`.`m_5cce2edad1fe49c3be3c82cc4d97107f`, 0), 0.0) <= 0.5 THEN `t2`.`a_label_dim_sidd_user_ids_local_user_id_tenant__1ba7abb141a9ba` ELSE NULL END) `m_3f241f772f07528ad84b833f6e074664`, COUNT(CASE WHEN COALESCE(`t7`.`def_m_9e925cbf1091294fd907261015c6ad8a`, FALSE) AND COALESCE(`t7`.`def_m_5cce2edad1fe49c3be3c82cc4d97107f`, FALSE) AND 0.5 < COALESCE(CAST((`t7`.`m_9e925cbf1091294fd907261015c6ad8a`) AS DOUBLE) / NULLIF(`t7`.`m_5cce2edad1fe49c3be3c82cc4d97107f`, 0), 0.0) AND COALESCE(CAST((`t7`.`m_9e925cbf1091294fd907261015c6ad8a`) AS DOUBLE) / NULLIF(`t7`.`m_5cce2edad1fe49c3be3c82cc4d97107f`, 0), 0.0) <= 0.85 THEN `t2`.`a_label_dim_sidd_user_ids_local_user_id_tenant__1ba7abb141a9ba` ELSE NULL END) `m_f4920fd4d062a82b008b212aaf171d29`, COUNT(CASE WHEN COALESCE(`t7`.`def_m_9e925cbf1091294fd907261015c6ad8a`, FALSE) AND COALESCE(`t7`.`def_m_5cce2edad1fe49c3be3c82cc4d97107f`, FALSE) AND 0.85 < COALESCE(CAST((`t7`.`m_9e925cbf1091294fd907261015c6ad8a`) AS DOUBLE) / NULLIF(`t7`.`m_5cce2edad1fe49c3be3c82cc4d97107f`, 0), 0.0) THEN `t2`.`a_label_dim_sidd_user_ids_local_user_id_tenant__1ba7abb141a9ba` ELSE NULL END) `m_dbc5d9ac578be404c12420fcd3baaacd`, MAX(CASE WHEN COALESCE(`t7`.`def_m_9e925cbf1091294fd907261015c6ad8a`, FALSE) AND COALESCE(`t7`.`def_m_5cce2edad1fe49c3be3c82cc4d97107f`, FALSE) AND COALESCE(CAST((`t7`.`m_9e925cbf1091294fd907261015c6ad8a`) AS DOUBLE) / NULLIF(`t7`.`m_5cce2edad1fe49c3be3c82cc4d97107f`, 0), 0.0) = 0 THEN 0 ELSE NULL END) IS NOT NULL `def_m_7768b660d2e0dc3e96dff8ca0d2c7d1f`, MAX(CASE WHEN COALESCE(`t7`.`def_m_9e925cbf1091294fd907261015c6ad8a`, FALSE) AND COALESCE(`t7`.`def_m_5cce2edad1fe49c3be3c82cc4d97107f`, FALSE) AND 0 < COALESCE(CAST((`t7`.`m_9e925cbf1091294fd907261015c6ad8a`) AS DOUBLE) / NULLIF(`t7`.`m_5cce2edad1fe49c3be3c82cc4d97107f`, 0), 0.0) AND COALESCE(CAST((`t7`.`m_9e925cbf1091294fd907261015c6ad8a`) AS DOUBLE) / NULLIF(`t7`.`m_5cce2edad1fe49c3be3c82cc4d97107f`, 0), 0.0) <= 0.5 THEN 0 ELSE NULL END) IS NOT NULL `def_m_3f241f772f07528ad84b833f6e074664`, MAX(CASE WHEN COALESCE(`t7`.`def_m_9e925cbf1091294fd907261015c6ad8a`, FALSE) AND COALESCE(`t7`.`def_m_5cce2edad1fe49c3be3c82cc4d97107f`, FALSE) AND 0.5 < COALESCE(CAST((`t7`.`m_9e925cbf1091294fd907261015c6ad8a`) AS DOUBLE) / NULLIF(`t7`.`m_5cce2edad1fe49c3be3c82cc4d97107f`, 0), 0.0) AND COALESCE(CAST((`t7`.`m_9e925cbf1091294fd907261015c6ad8a`) AS DOUBLE) / NULLIF(`t7`.`m_5cce2edad1fe49c3be3c82cc4d97107f`, 0), 0.0) <= 0.85 THEN 0 ELSE NULL END) IS NOT NULL `def_m_f4920fd4d062a82b008b212aaf171d29`, MAX(CASE WHEN COALESCE(`t7`.`def_m_9e925cbf1091294fd907261015c6ad8a`, FALSE) AND COALESCE(`t7`.`def_m_5cce2edad1fe49c3be3c82cc4d97107f`, FALSE) AND 0.85 < COALESCE(CAST((`t7`.`m_9e925cbf1091294fd907261015c6ad8a`) AS DOUBLE) / NULLIF(`t7`.`m_5cce2edad1fe49c3be3c82cc4d97107f`, 0), 0.0) THEN 0 ELSE NULL END) IS NOT NULL `def_m_dbc5d9ac578be404c12420fcd3baaacd`
FROM (SELECT `local_user_id` `a_label_dim_sidd_user_ids_local_user_id_tenant__1ba7abb141a9ba`
FROM `gooddata_v3`.`dim_sidd_user_ids`
WHERE `wdf__tenant_id` = "sephora"
GROUP BY `local_user_id`) `t2`
LEFT JOIN (SELECT `t4`.`local_user_id` `a_label_dim_sidd_user_ids_local_user_id_tenant__1ba7abb141a9ba`, SUM(`t3`.`f__call_case_note_ended`) `m_9e925cbf1091294fd907261015c6ad8a`, SUM(`t3`.`f__call_transcription_started`) `m_5cce2edad1fe49c3be3c82cc4d97107f`, TRUE `def_m_9e925cbf1091294fd907261015c6ad8a`, TRUE `def_m_5cce2edad1fe49c3be3c82cc4d97107f`
FROM (SELECT *
FROM `gooddata_v3`.`sidd_analytic_standard_rollup_sentiment_call_cases`
WHERE `wdf__tenant_id` = "sephora") `t3`
INNER JOIN (SELECT *
FROM `gooddata_v3`.`dim_sidd_user_ids`
WHERE `wdf__tenant_id` = "sephora") `t4` ON `t3`.`wdf__tenant_id` = `t4`.`wdf__tenant_id` AND `t3`.`userids_id` = `t4`.`userids_id`
GROUP BY `t4`.`local_user_id`) `t7` ON `t2`.`a_label_dim_sidd_user_ids_local_user_id_tenant__1ba7abb141a9ba` = `t7`.`a_label_dim_sidd_user_ids_local_user_id_tenant__1ba7abb141a9ba`
GROUP BY CAST(NULL AS INTEGER)) `t10`
WHERE `t10`.`def_m_7768b660d2e0dc3e96dff8ca0d2c7d1f` OR `t10`.`def_m_3f241f772f07528ad84b833f6e074664` OR `t10`.`def_m_f4920fd4d062a82b008b212aaf171d29` OR `t10`.`def_m_dbc5d9ac578be404c12420fcd3baaacd`;
with date filter Its now 130 Lines (can't post the entire thing its too long
more than 170 lines and with a lot of unions
When I removed this part of the query it worked
Copy code
SELECT     count(DISTINCT
                                                             CASE
                                                                        WHEN COALESCE(`t42`.`def_m_f112d255586fa660a8bc3086a761636e`, false)
                                                                        AND        COALESCE(`t42`.`def_m_7ca863ba6e2fee4f7a9018deaa0be172`, false)
                                                                        AND        COALESCE(cast((`t42`.`m_f112d255586fa660a8bc3086a761636e`) AS DOUBLE) / NULLIF(`t42`.`m_7ca863ba6e2fee4f7a9018deaa0be172`, 0), 0.0) = 0 THEN `t36`.`local_user_id`
                                                                        ELSE NULL
                                                             END) `m_3ce18810f3467387979f344ffcf3eb11`,
                                                             count(DISTINCT
                                                             CASE
                                                                        WHEN COALESCE(`t42`.`def_m_f112d255586fa660a8bc3086a761636e`, false)
                                                                        AND        COALESCE(`t42`.`def_m_7ca863ba6e2fee4f7a9018deaa0be172`, false)
                                                                        AND        0 < COALESCE(cast((`t42`.`m_f112d255586fa660a8bc3086a761636e`) AS DOUBLE) / NULLIF(`t42`.`m_7ca863ba6e2fee4f7a9018deaa0be172`, 0), 0.0)
                                                                        AND        COALESCE(cast((`t42`.`m_f112d255586fa660a8bc3086a761636e`) AS DOUBLE)     / NULLIF(`t42`.`m_7ca863ba6e2fee4f7a9018deaa0be172`, 0), 0.0) <= 0.5 THEN `t36`.`local_user_id`
                                                                        ELSE NULL
                                                             END) `m_a4291d22c663dc39a4c453ff1058b4bd`,
                                                             count(DISTINCT
                                                             CASE
                                                                        WHEN COALESCE(`t42`.`def_m_f112d255586fa660a8bc3086a761636e`, false)
                                                                        AND        COALESCE(`t42`.`def_m_7ca863ba6e2fee4f7a9018deaa0be172`, false)
                                                                        AND        0.5 < COALESCE(cast((`t42`.`m_f112d255586fa660a8bc3086a761636e`) AS DOUBLE) / NULLIF(`t42`.`m_7ca863ba6e2fee4f7a9018deaa0be172`, 0), 0.0)
                                                                        AND        COALESCE(cast((`t42`.`m_f112d255586fa660a8bc3086a761636e`) AS DOUBLE)       / NULLIF(`t42`.`m_7ca863ba6e2fee4f7a9018deaa0be172`, 0), 0.0) <= 0.85 THEN `t36`.`local_user_id`
                                                                        ELSE NULL
                                                             END) `m_f3a540aadd9c2ac0dd32d316b7f29f17`,
                                                             count(DISTINCT
                                                             CASE
                                                                        WHEN COALESCE(`t42`.`def_m_f112d255586fa660a8bc3086a761636e`, false)
                                                                        AND        COALESCE(`t42`.`def_m_7ca863ba6e2fee4f7a9018deaa0be172`, false)
                                                                        AND        0.85 < COALESCE(cast((`t42`.`m_f112d255586fa660a8bc3086a761636e`) AS DOUBLE) / NULLIF(`t42`.`m_7ca863ba6e2fee4f7a9018deaa0be172`, 0), 0.0) THEN `t36`.`local_user_id`
                                                                        ELSE NULL
                                                             END) `m_cd0140b2366a052339fe33e775022b31`
                                                  FROM       (
                                                                    SELECT *
                                                                    FROM   laivly.`gooddata_v3`.`sidd_analytic_standard_line_call_topic`
                                                                    WHERE  `wdf__tenant_id` = "sephora") `t35`
                                                  INNER JOIN
                                                             (
                                                                    SELECT *
                                                                    FROM   laivly.`gooddata_v3`.`dim_sidd_user_ids`
                                                                    WHERE  `wdf__tenant_id` = "sephora") `t36`
                                                  ON         `t35`.`wdf__tenant_id` = `t36`.`wdf__tenant_id`
                                                  AND        `t35`.`userids_id` = `t36`.`userids_id`
                                                  LEFT JOIN
                                                             (
                                                                        SELECT     `t38`.`local_user_id` `a_label_dim_sidd_user_ids_local_user_id_tenant__1ba7abb141a9ba`,
                                                                                   sum(`t37`.`f__call_case_note_ended`) `m_f112d255586fa660a8bc3086a761636e`,
                                                                                   sum(`t37`.`f__call_transcription_started`) `m_7ca863ba6e2fee4f7a9018deaa0be172`,
                                                                                   true `def_m_f112d255586fa660a8bc3086a761636e`,
                                                                                   true `def_m_7ca863ba6e2fee4f7a9018deaa0be172`
                                                                        FROM       (
                                                                                          SELECT *
                                                                                          FROM   laivly.`gooddata_v3`.`sidd_analytic_standard_rollup_sentiment_call_cases`
                                                                                          WHERE  `wdf__tenant_id` = "sephora") `t37`
                                                                        INNER JOIN
                                                                                   (
                                                                                          SELECT *
                                                                                          FROM   laivly.`gooddata_v3`.`dim_sidd_user_ids`
                                                                                          WHERE  `wdf__tenant_id` = "sephora") `t38`
                                                                        ON         `t37`.`wdf__tenant_id` = `t38`.`wdf__tenant_id`
                                                                        AND        `t37`.`userids_id` = `t38`.`userids_id`
                                                                        WHERE      `t37`.`case_opened` >= convert_timezone("Etc/UTC", current_timezone(), to_timestamp("2025-05-07", "yyyy-MM-dd"))
                                                                        AND        `t37`.`case_opened` < convert_timezone("Etc/UTC", current_timezone(), (to_timestamp("2025-05-07", "yyyy-MM-dd") + (interval '1 day')))
                                                                        GROUP BY   `t38`.`local_user_id`) `t42`
                                                  ON         `t36`.`local_user_id` = `t42`.`a_label_dim_sidd_user_ids_local_user_id_tenant__1ba7abb141a9ba`
                                                  WHERE      `t35`.`case_opened` >= convert_timezone("Etc/UTC", current_timezone(), to_timestamp("2025-05-07", "yyyy-MM-dd"))
                                                  AND        `t35`.`case_opened` < convert_timezone("Etc/UTC", current_timezone(), (to_timestamp("2025-05-07", "yyyy-MM-dd") + (interval '1 day')))
                                                  AND        (
                                                                        COALESCE(`t42`.`def_m_f112d255586fa660a8bc3086a761636e`, false)
                                                             AND        COALESCE(`t42`.`def_m_7ca863ba6e2fee4f7a9018deaa0be172`, false)
                                                             AND        COALESCE(cast((`t42`.`m_f112d255586fa660a8bc3086a761636e`) AS DOUBLE) / NULLIF(`t42`.`m_7ca863ba6e2fee4f7a9018deaa0be172`, 0), 0.0) = 0
                                                             OR         COALESCE(`t42`.`def_m_f112d255586fa660a8bc3086a761636e`, false)
                                                             AND        COALESCE(`t42`.`def_m_7ca863ba6e2fee4f7a9018deaa0be172`, false)
                                                             AND        0 < COALESCE(cast((`t42`.`m_f112d255586fa660a8bc3086a761636e`) AS DOUBLE) / NULLIF(`t42`.`m_7ca863ba6e2fee4f7a9018deaa0be172`, 0), 0.0)
                                                             AND        COALESCE(cast((`t42`.`m_f112d255586fa660a8bc3086a761636e`) AS DOUBLE)     / NULLIF(`t42`.`m_7ca863ba6e2fee4f7a9018deaa0be172`, 0), 0.0) <= 0.5
                                                             OR         COALESCE(`t42`.`def_m_f112d255586fa660a8bc3086a761636e`, false)
                                                             AND        COALESCE(`t42`.`def_m_7ca863ba6e2fee4f7a9018deaa0be172`, false)
                                                             AND        0.5 < COALESCE(cast((`t42`.`m_f112d255586fa660a8bc3086a761636e`) AS DOUBLE) / NULLIF(`t42`.`m_7ca863ba6e2fee4f7a9018deaa0be172`, 0), 0.0)
                                                             AND        COALESCE(cast((`t42`.`m_f112d255586fa660a8bc3086a761636e`) AS DOUBLE)       / NULLIF(`t42`.`m_7ca863ba6e2fee4f7a9018deaa0be172`, 0), 0.0) <= 0.85
                                                             OR         COALESCE(`t42`.`def_m_f112d255586fa660a8bc3086a761636e`, false)
                                                             AND        COALESCE(`t42`.`def_m_7ca863ba6e2fee4f7a9018deaa0be172`, false)
                                                             AND        0.85 < COALESCE(cast((`t42`.`m_f112d255586fa660a8bc3086a761636e`) AS DOUBLE) / NULLIF(`t42`.`m_7ca863ba6e2fee4f7a9018deaa0be172`, 0), 0.0))
                                                  GROUP BY   cast(NULL AS integer)
This union joins the other 3. Unsure why its there
@Joseph Heun does any of the above help? I think the issue is these unions and that two of the unions create values - instead of just one (why there is so many unions is strange to me)
j
Hey @Doug Creighton this certainly helps. We will look into the WDF and the sql being called here.
d
@Joseph Heun @Yvonne Changamuka are customers are complaing a lot about this. Did you make any progress? I really don't think its our model this time, we have followed all your best practices.
Is there any way we can escalate this?
Just FYI - I have a workaround that works for now. I added the userids as an attribute to a fact table and added it there. So the error still exists here: https://reporting.laivly.cloud/dashboards/#/workspace/tenant-sephora-v3/dashboard/6386d817-f822-4d2c-a1f0-70d2072fba9b
d
@Doug Creighton Joe and Yvonne escalated it, I've been looking at your dashboards, but have nothing to report yet.
✅ 1
j
Hi Doug, I see that the correct value of 5 is displaying now in the original dashboard. Can you please confirm what exact changes you had made to get this to work? Also, do these changes not work for the dashboard here: https://reporting.laivly.cloud/dashboards/#/workspace/tenant-sephora-v3/dashboard/6386d817-f822-4d2c-a1f0-70d2072fba9b
d
@Joseph Heun I mentioned it a message above "I added the userids as an attribute to a fact table and added it there." This forces it to count distinct i have found. its not the right solution there I should be just doing that on the dimension table because now I need to do that to every fact table that uses histograms on usersid
If you have questions on the above, let me know
j
Ok, we are trying to get an idea of what should be displaying in this viz here: https://reporting.laivly.cloud/analyze/#/tenant-sephora-v3/a13ca279-2886-431d-abee-820b4dabf5f4/edit Previously, the incorrect count was 8, and it should have been 5. Here, I'm seeing the count for test-none is 3. Could you please confirm what is the expected value in the above test report?
d
The test date was May 7th not July 5th.
Yes, it should be 5 on that date
j
Ok, sorry... the most recent link you had provided showing it not working was using July 5th date, so there was a bit of confusion after that. Can you please provide a link to the viz where it is working as you expect it to? At this point, we would like to see the version of the following graph where you say it is working correctly?
is it just comparing the "test - none" and "none" values now? I'm sorry, but this seems to have changed since i have viewed it last
Actually, I see that is not correct because they are counting two different things.
d
@Joseph Heun I will add in the metric that was what we had before
https://reporting.laivly.cloud/dashboards/#/workspace/tenant-sephora-v3/dashboard/6386d817-f822-4d2c-a1f0-70d2072fba9b I added the one where it counts on the Dimension table back. This should equal 5 but does not. The other ones now count an attribute on the fact table (which does work) However, the first way is the "right" way I think and should work. My current solution is a work around.
j
Hi Doug, I just wanted to let you know we are double-checking this behavior with engineers and will follow up with more findings as soon as possible. At this time, could you please provide a link to the bar chart where you have the metric working as expected?
Hi Doug, so I think the issue at hand here is that the metric should be defined as the following: SELECT count(Userids id, Userids id) WHERE Utilized % - Spark Case Notes = 0 However, this is causing some errors in the interaction with the filter, but I'm not sure exactly what. We are currently looking into this but I just wanted to see if this was how the metric was defined which is working in other workspaces?
d
In the error board I showed, I have it working and not working. The way its working - is adding the userids_id in the fact table as I said above. Any questions on it
These are work as they are supposed to now because of the above None, Low, Medium, High and Test - None - 3. https://reporting.laivly.cloud/dashboards/#/workspace/tenant-sephora-v3/dashboard/6386d817-f822-4d2c-a1f0-70d2072fba9b
I don't get this one. as there is no group by user_id - how does it now what rows to do the utilized % on
Copy code
SELECT count(Userids id, Userids id) WHERE Utilized % - Spark Case Notes = 0
d
Hi Doug, Joe handed this question over to me. I've read the history here. But many things changed in the progress and I can't get a clear picture. Can we start from scratch, please? What is the current situation? Do we have two metrics which should produce the same result but don't? What are their names? Can we put them on one dashboard to see them side-by-side and compare? (Is there such a dashboard already?) Can we break the problematic metric into smaller parts to see which part doesn't behave as expected?
d
Nothing has changed in the process.
https://reporting.laivly.cloud/dashboards/#/workspace/tenant-sephora-v3/dashboard/6386d817-f822-4d2c-a1f0-70d2072fba9b I have labled them - Old SHould work - is what we had before Joseph says should work but has errors - is what he said should work The rest is the workaround that I made for now
The issue is that when we are counting unique usersids on the userids dimension table its double counting some. I looked at the SQL in the above comment and when I filter by date the SQL gets massive and does unions it does not need to. One of those unions adds users to the count. With no date filter the SQL is more or less as expected My workaround was to take the usersid_id that is a dimension table and then put it on the Fact table which forces gooddata to add a count distinct
The way that should work
Copy code
SELECT count({label/dim_sidd_user_ids.userids_id}) <- counting on the dimension table 
WHERE(
SELECT {metric/utilized_-_spark_case_notes}
BY {label/dim_sidd_user_ids.userids_id} <- this and local_user_id are unique on the wdf filter so gives the same answer
) = 0
What is working - but its a workaround that creates a bit of a headache. We shouldn't have to add the dimension to a fact table
Copy code
SELECT count({label/case_userids}) <- counting on the fact table
WHERE(
SELECT {metric/utilized_-_spark_case_notes}
BY {label/dim_sidd_user_ids.local_user_id} <- this and local_user_id are unique on the wdf filter so gives the same answer
) = 0
What Joseph said should work (I personally don't understand the logic here) - this one creates errors in the filters
Copy code
SELECT count({label/dim_sidd_user_ids.userids_id}, {label/dim_sidd_user_ids.userids_id})
WHERE {metric/utilized_-_spark_case_notes}
= 0
d
Thanks for the detail, Doug 👍 I'm looking at the dashboard. I suppose the "Test - Case ..." viz in the top-left corner is the one demonstrating the issue. Right? The date you use for testing is May 7th, correct? (NOT July 5th, right?) I know it happens for other dates too, but let's both look at the same one. Two metrics don't show anything, let's ignore them. 
test - None
is the one that should work but gives wrong numbers? 
None
and 
Test - None 3
are the ones that are showing correct number but are confusing?
OK, I can see in the viz now: 
test - None
is the one that calculates incorrectly, 
None
is the one that works but is confusing (headache) 
Test - Should be 5 May
is the one that Joe suggested and the filters object to it Right?
d
Looks like I didn't save my cahanges
One second
I updated the names to how you described above Except None,
d
Hi Doug, I've been looking into your metric from different angles. I haven't solved it yet, I'm afraid. I have a couple observations - maybe they'll help you figure it out. 1. Your metric seems to generate a ROLLUP rather than a simple COUNT. I suspect something is wrong with userids ID in your fact table. If you count by Case Userids instead, you get the correct numbers. Originally, I suspected it was because your User dimension is not directly connected to your dates. But that doesn't seem to be related - the problem remains even if you completely remove the date dimension from the viz. 2. There seems to be an issue with specific data. For example, if you only use users with ID 5230, 5390, and 5400 all is fine. But as soon as you add 11180, each of the other ones is suddenly counted twice. WHY? Can you check the underlying data for inconsistencies? Specifically, the use of Userids ID in your fact table seems suspicious. 🤔 I'm sorry I can't point you to the specific issue. I don't see your underlying data and I don't understand your model well. So I'm fishing blind here 😢 I do hope this helps lead you in your investigation.
d
I don't know what you mean by suspicious but the table only has one unique ID per row
The model's pretty simple there are a bunch of dimension tables and a bunch of fact tables
We have a duplicate set of fact tables that are Roll-Ups for the day for faster queries
I've already sort of mentioned why there's double counting it's because it's doing all these unnecessary unions and I don't know why the good data SQL generator would do that
The funny thing is it only does these unions sometimes and not other times why I have no idea and I pointed out one of the cases above when I added the date filter in that case it added all of these extra unions and in one of those unions it double counted on some users
So for me the real mystery is not the underlying data or the data model it's why we're getting very different gooddata SQL queries when the filter logic changes. why sometimes does it do a simple query and other times it does a query with 10 unions
This is the underlying data for that usersid_ids
I sent in a dm
d
got it, thanks
d
If you could explain the question I have above. Why there are very different SQL quieres generated. For me, thats the major mystery. It does do the simple query most of the time then randomly does this query with 10 unions. When it does that we get the wrong number. I think I figured out the cause and effect of the wrong number. Just do not know why its doing that.
m
Hi Doug, I hope to have good news for you. After further investigation, we identified the issue, which is similar to the one shared by my colleague Daniela during your last request. First, to clarify, you mentioned that the metric '`Completed % - Spark Case Notes - None`' is showing the correct value (5) for May 7th as example. Based on that, we debugged the metric:
Copy code
SELECT count({label/case_userids}) 
WHERE( 
SELECT {metric/utilized_-_spark_case_notes} 
BY {label/dim_sidd_user_ids.local_user_id} 
) = 0
In this metric, you also have another metric called '`Utilized % - Spark Case Notes`':
Copy code
SELECT IFNULL(SELECT SUM({fact/sidd_analytic_standard_rollup_sentiment_call_cases.call_case_note_ended})/ 
SUM({fact/sidd_analytic_standard_rollup_sentiment_call_cases.call_transcription_started}),0)
This metric relies on two facts from the dataset: 
sidd_analytic_standard_rollup_sentiment_call_cases
. In your case, because the Logical Data Model is quite complex and the 
userids_id
appears in multiple fact tables/datasets, you need to use the ``USING`` clause to specify which dataset you are referring to. In this case, I believe you want to use the same dataset as the working metric, which is 
sidd_analytic_standard_rollup_sentiment_call_cases
(please, refer the example provided in the documentation) So, by updating the metric as follow:
Copy code
SELECT count({label/dim_sidd_user_ids.userids_id})
WHERE(
SELECT {metric/utilized_-_spark_case_notes}
BY {label/dim_sidd_user_ids.userids_id}
) = 0 
USING  {dataset/sidd_analytic_standard_rollup_sentiment_call_cases}
You will get a result of 5 for May 7th. To answer your question, with this change, all the unions and joins in the SQL query will be eliminated, simplifying it significantly. Previously, the other unions and joins were necessary because 
userids_id
is present in many fact tables. Please, give it a try and let us know.
👍 1
d
Yes that worked. Thanks
🙌 1
8 Views
Open in Slack
PreviousNext
Powered by