Fairly new to Quicksight but hoping you all can help me. I have 2 datasets, tickets and work orders. (both are also joined to a locations table but I don’t think that is causing the problem). Work orders is a subset of tickets. Not every ticket has a work order associated with it but every work order has a parent that is a ticket. I want to count the number of work orders per ticket in a visualization about tickets. I have tried to do a countOver({Work Order Number}, [{Work Order Parent Ticket Number}], PRE_AGG) calculated field in the work order dataset and then join that to tickets via the ticket id and work order parent fields. But this creates a cartesian join and I get multiple ticket rows when multiple work orders are present for that ticket. I have tried a few other methods suggest by 3 different AI helpers without success. I am loading csv files via s3 buckets and manifest files. Was not ready to go the Athena route yet with SQL. Is there some way to accomplish what I am trying to do with the current setup? Thanks!
Hi @MPittsPV and welcome to the QuickSight community!
What if you tried a distinctCountOver calculation instead of countOver?
Let me know if this works for your case or if you have any additional questions!
Thank you for the suggestion. I did end up finding an issue with my Locations data (using dummy data and it somehow got duplicated). Fixed that. Made sure my tickets were unique. But when I add the calculation, even with distinctCountOver to the calculated field in work orders, and then join the work orders table, it is creating a Cartesian join still. Multiple ticket rows if multiple work orders are matched. Part of the problem I see is that if I include the work order unique id in the countOver calculated field, then I have to include it in the work orders dataset that gets joined to tickets. I am only included the calculated field, the work order id, and the parent ticket id in the dataset before joining. So I feel like that is creating multiple rows. There does not seem to be a way to “group by” the parent id in work orders to the tickets.
Hi @MPittsPV,
Why is it a problem if the joined dataset created multiple rows per ticket? I think if you join the 2 datasets and run the distinctCountOver calculation after the join, you would have better luck. Then you can use the calculation like this:
Work Orders per Ticket = distinctCountOver({Work Order ID}, [{Ticket ID}], PRE_AGG)
I would also recommend adding this calculation at the analysis level instead of the dataset level because I think it will function better that way.
Hi @MPittsPV,
It’s been awhile since we last heard from you; following up to see if you had any additional questions?
If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.
Thank you!
Hi @MPittsPV,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.
Thank you!