I was curious if someone else has encounter a similar issue.
I have 3 datasets on quicksight, that are counting annual data. one of the datasets is 100k, but by the quicksight behavior on aggregating the data only 30k are displaying in the dashboard out of 100k. if I count the number of records, it in fact counts the 100k but when displaying them it doesn’t go more than the 30k. is there any way I can actually display the actual values and not aggregations? I have tried the count distinct within each field, but that won’t work either because is going to throw. records that need to be displayed.
The data comes from a redshift view - from this point I have tried row_number function with partition over still is counting the right amount of data, but not displaying it on QS. I was suggested to create a PK within the redshift view, but you cannot create PKs on views according to Redshift documentation.
in any case the issue is not on Redshift because I get the right counting of records is when it gets ingested on QS that the rows get aggregated.
Hello @Wvelasqu, I totally understand your frustration. Unfortunately with every visual there is some kind of limit to the amount of data that you are able to display. I’m assuming in this situation, you are trying to display all of the data you are querying into a table or something, am I correct? It might be worth setting up some kind of parameter controlled filter to display your data in groups, rather than trying to show all 100K records.
In situations I have faced, sometimes there is an obvious way to group your data like location, client name, district, or something, but in scenarios where there are not, I try to group them in ways that might be helpful to the reader. You could try building a function that would show things based on percent of sales, size of a client, etc. and use an ifelse calculated field to return certain rows based on the situation.
I know this isn’t a perfect solution, but it is how I have tried to manage these situations in my own projects. If you can think of something that you would be able to filter on and you want some guidance I am happy to help!
I’ll link some documentation as well for parameter controlled filters in QuickSight in case that helps guide you towards a solution!
Thanks for the suggestion. I do have to use all of the column fields in my datasets (is requirement by the SMEs) and yes, I do use filters with parameters.
the issue is that my data came aggregated in the display instead of displaying the 100k rows it only displayed ~49k. I’m bringing over this data from a redshift view I have counted the number of records, and it does show the 100k. I also did the counting in QuickSight and even the info banner shows are 100k record imported. I have been digging on this and was able to find out the issue as it is aggregating the Null rows that do not have a lead column (let’s say an id that identify them, but it has data in other columns that have actual values) and I was able to bring over the 100k rows in the display.
I’m still correcting that part on my end since that is based on some requirements by the SMEs (how the data is limited and that is why the lead columns come over as null, realized is a correction I have to do in the redshift view, so it does not aggregate the values in the QuickSight import)
I hope I’m making sense, of what I’m explaining you.
in any case thank you very much for the suggestion. really appreciate you taking the time!
Hello @Wvelasqu, my apologies for the delayed response! Is this something you are still experiencing an issue with? I am wondering if it possibly an issue with how the joins are being calculated if some of the rows are missing an identifier to link them to the table view.
If you are still having an issue finding a solution to this, I think it would also be helpful to submit a ticket to AWS Support so that they can dig into the details of this issue a little further. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf. I hope this helps!
No worries for the delay, it gave time to review what I was doing. I did open a support ticket and was recommended to rework the redshift views I’m bringing over to QuickSight and add Primary Keys/unique row to it. To remove the aggregation that happens in the display. So, I did a couple of things:
I re-work my views (first without the unique row value using the row function on SQL) still got the same issue it kept aggregating the values of the rows
re-work my views (secondly using the row function in SQL) and here I found what was going on. My data has some complexity and along to that it has skewedness many of the aggregated values were not showing up because the main identifiers were with null, I had to do a few tweaks with the joins in the data, so those nulls had at least an identifier. my stakeholders could look at when validating the data.
I’m usually the one who works with the AWS team from my company but needed to test different scenarios before adding more evidence in the ticket, but my approach worked.
Thank you for checking though, very well appreciated on this end!
Hello @Wvelasqu, I am really happy to hear that you were able to resolve this and I seriously appreciate you taking the time to let us know the steps you took to implement the solution. Thank you for being so understanding!