Hi. I am facing an issue with some of the data displayed in the table. I have 2 data sources in my data set. 1) Orders 2) Order Items. So for example, If I have an order with 2 order items with 1 item being $100 and other being $0. However when I put the data on the chart able, that order displays as order total of $200. The correct order total should be $100… Has anyone experienced this issue? If so any solution to fix this? Thanks for your input!!
Are there two rows with 100?
And what fields are you joining on?
It probably has something to do with the joins.
I would confirm that the amount of rows you are returning is what you are expecting.
My suggestion would be to filter down to one order and display all of the fields and see if there are any fields that are duplicating because of your joins.
Hey Max. Thanks for your reply. I did create a new test datasource with same relationship and it is def doubling the amount, however there’s only 1 record for that order number…
This is supposed to be $1599…
Can you also show me a count next to the orderSubtotal? You can take count of orderSubtotal.
Also, depending on how you want to show your visuals you could take the avg / avgOver and partition by orderNumber. It will cause limitations but it will get you your end result.
In the end, I’m guessing it has to do something with the join
I truly appreciate for your help debugging this issue. As you suspected, there are 2 counts. Def it’s multiplying due to the join issue. There are 2 orderLineItems in this order so it makes sense.
Max. I changed the JOIN from left join to right join and the orderSubtotal and the count is correct. Did I pick the wrong JOIN???
Haha it seems like it. Hope that fixes it!
Sorry I take it back. The data as still loading. Went back to 3199 and count of 2. I added the table to display records from OrderLineitems and is def showing 2 records. Also when I placed the orderSubtotal, which is a field in the Order table, it both shows 1599.95. So it make sense it’s adding both up to 3199.90. I also tried to change the join type to Inner, left, right and both and all had the same result. I am a bit lost in this.
In your orders table do you have a field that has orderlineitemId that will get you the order price of that one item?
Essentially you need to join on the unique ID or else it will bring in all fields (including duplicates) based on the field.
You can look at this for more information.
Also, is there an order amount based on the line item in the order line items table?
Max, Thanks for the w3school info. I did take a look at it. I believe I found the issue… I had the orderSubtotal in the values column and so it was adding the 2 orderLineItems and coming up with 3199. I did move the orderSubtotal field under Rows and now it is showing the correct amount. I am still new to Quicksight and am learning how everything works. I appreciate for your help and all the input you have given me!!
1 Like