Calculate a field avoiding duplicate expense lines

Question & Answer
, ,
1

I’m trying to calculate a field, this is mine at this moment

And I use it on the most of my cards, but on the graphic it doesn’t work

After doing a left join between two tables in my database, I got duplicate rows in my dataset, so I made the field calculate.

** The expense has 2 apportionments, and after the relationship between the tables, the expense is duplicated in the table we use for QuickSight

*First image - Calculate Field
*Second - Graphic doens’t working
*Third - Duplicated line

image
image1872×954 104 KB

And I want to use the same field into the Graphic, however I can use another to use too.

Could you help me with this calculation?
I’ve tried a lot of diffents ways to solve this, other kind of calculations and so on.

Or give me another way to calculate it, pls

2

Hello @Gabriel_CS, welcome to the QuickSight community!

I may need a little more information about the error you are receiving from the visual since I can’t understand the message, but I think I know what the issue is. Likely, you are getting that error from the visual because you are including the partition of ID in your calculated field, but then not using that field in your visual. Due to this, QuickSight doesn’t understand how to display that data. If you are building a table visual, you can add ID into the field well and then select “Hide” on the field options so it doesn’t display in the table. Then your calculated field should work as you are expecting.

Let me know if that helps or if you have any follow-up questions. Thank you!

3

Hi @DylanM, I’ve solved my problem.

What did I do?

  • I’ve created a new table in data set, using a custom sql, after this, I sum the main field to get the result and It’s working now.

What couln’t I do?

  • Edit a custom field to get the sum from my main data base. I couln’t avoid the duplicate rows.

Below you can see the new data base in my data set.

image
image736×413 19.3 KB

Thanks.

1 Like
4

Hello @Gabriel_CS, thank you for posting your solution. I’m glad you were able to get it to work.