Goodnight! I will try to explain my problem in a simplified way. I have a dataset that contains the corrective occurrences of my equipment, in another I have the accesses to my equipment. I want to create a graph that gives me the volume of occurrences divided by the accesses. However, I want it to be possible to filter my occurrences, for example, I have 10000 occurrences in the current month, and I have 1000 hits, so it will show 10, if I filter occurrence X which has 1000 occurrences, then it will show 1. Another point , I also want to filter the equipment, this equipment appears in both bases, so, when filtered, it shows the accesses and occurrences of this specific equipment. In PowerBi I can do it through the relational model, but so far I haven’t managed it in Quicksight, because when I do a JOIN and filter a column, I consequently “desconsider” information from one side of my dataset. Thanks in advance.
Hello @Lucas1, welcome to the Quick Sight community!
Using LAC-W aggregations in Quick Sight, you should be able to accomplish this. How are you calculating your occurrences and accesses? If these values require distinct counts of IDs or you are using sum to gain a total value, either one should work in this scenario. I’ll provide an example below of how you would calculate this and it should be managed by your filters.
sumOver({occurrences}, [], PRE_AGG)/sumOver({accesses}, [], PRE_AGG)
The empty brackets for the partition should reference all available rows, and those rows should be filtered before utilized in the aggregation. Let me know if this helps!
Olá Dylan! This is my example dataset: Date Equipment Occurence Access
21/04/2024 10000 X 100
21/04/2024 10000 Y 100
21/04/2024 10001 X 100
22/04/2024 10000 X 100
23/04/2024 10000 X 100
24/04/2024 10000 X 100
25/04/2024 10000 X 100
26/04/2024 10000 X 100
27/04/2024 10000 Y 100
28/04/2024 10000 Y 100
29/04/2024 10000 Y 100
30/04/2024 10000 Y 100
01/05/2024 10000 Y 100
02/05/2024 10000 Y 100
03/05/2024 10001 Y 100
04/05/2024 10000 Y 100
05/05/2024 10000 Y 100
I want to count the occurrences, where each row is one occurrence, and then divide these occurrences by the number of accesses on the day. I put both in the same dataset to do a sumOver test. My accesses are duplicated, for example, on 04/21/2024, I have two rows for each occurrence and in these two rows there are 100 accesses, so for this equipment, on this day, I had 100 accesses. Basically, the value is grouped by Date and equipment. However, I want a line chart with the daily value, and when I do this, I get the message: Table calculation attribute references are missing in field wells quicksight.
Expected result:
Sorry, I forgot to mention that after getting this correct access value, I will divide the occurrence count by it.
Hello @Lucas1, I see, I think I understand now. Basically, you will use countOver to calculate the 2 values and aggregate them to receive the percent value you want. Then you can plot them as the value on the line graph with the date aggregated by day as your x-axis. I’ll show the calculated field below:
I am not sure what your fields are called, but you will want to use one of the fields other than your date as the aggregated value in the function so you can partition it by date.
countOver({field}, [{Date}], PRE_AGG)/countOver({field}, [], PRE_AGG)
The numerator will calculate the total accesses for a single date and it will be divided by the total number of occurrances. I will mark this as the solution, but let me know if you have any remaining questions. Thank you!
Thanks you very much!