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.
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
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.
Cheers,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
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.
Cheers,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
Thank you Dustin for your response.
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
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.
Cheers,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
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.
Cheers,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
Thanks Dustin for your feedback,
I actually made a work around to get the values I wanted, but I hope you guys can come up with an additional feature for these kind of calculations.
Best Regards;
-Mauricio
I actually made a work around to get the values I wanted, but I hope you guys can come up with an additional feature for these kind of calculations.
Best Regards;
-Mauricio
Hi Mauricio,
Could you let me know the work around you used? I'm interested to hear what you did. Thanks in advance for the feedback.
Cheers,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
Could you let me know the work around you used? I'm interested to hear what you did. Thanks in advance for the feedback.
Cheers,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
Thanks Dustin,
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.
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.
