Forum

sum_distinct_values

14 July, 2015

Hi There,

Attached you'll find a table that displays distinct counts per day, and I am trying by all means to get the sum of distinct values within the entire selection. What the report gives me is a sum of daily distinct values, but how can I get a real sum of distinct values across all dates? For the distinct_customer_id column the real value should be 11 whereas the distinct_device_id column value should be 17.

Hi,

Would you please take a look at this post? I need to deliver a set of dashboards based on my request.

Thank you,
Mauricio

Hi Mauricio,

I'm sorry that you are running into problems getting your sub totals to appear correctly.

Unless I am misunderstanding what you are trying to accomplish, it appears that your columns are counting distinct by each unique date in your date column. Do you think that you could try removing the date column and instead add it to your report as a filter. Does this produce results that you are after?

If this doesn't work, could you remove all of your report aggregations and export your report to .csv then attach it to this forum post so that we can try to produce what you are after?

Please let me know when you find a moment. I look forward to hearing back.

I don't see viable removing dates from my report as my ultimate goal is to generate a series selection chart which date is the horizontal axis.

Attached you'll find the csv file for my particular dashboard, and a print screen of the chart I am going after.

Best regards;

Mauricio

Here is the print screen of my report

Hi Mauricio,

Unfortunately what you are trying to accomplish just is not possible. When combining aggregate columns (count distinct()) with other non aggregated columns (date) a group by clause will always and must be included in the underlying SQL.

As far as the sub total goes, as you apply different aggregations to your columns the count distinct sub total is based on the new values for each row, and not the original fields (basically you are counting the distinct counts of your column).

The customer_Id column shows a sub total of 5 because there are now 5 unique fields in the column -- 1, 3, 4, 5, 6.

If you want to see the sub total for each particular customer/device ID by date you would need to remove the column aggregations and them apply the count distinct sub total.

I hope this clears things up, please let me know if you have any other questions/concerns.

Well, it was actually a matter of creating a master report as "big number" and from that point link all other reports. Because I can't aggregate active users, I created a sub-tab for active users only, and another sub-tab for metrics that can be aggregated, so that I could use series selection. Here is an example.