When pivoting the data it is counting or summing multiple times

[govindvasu]

Hi,

I have the following data 

Empid        Name           amount        year

1                      vasu              20                    2020

1                      vasu              30                   2022

1                      vasu              20                    2020

1                      vasu              120                    2021

1                      vasu              140                    2023

1                      vasu              350                    2022

when i pivot the data result has to show like this for the each specific year based on empid it has to sum the amount and has to display like as shown below

Empid        Name             2020                2021              2022                 2023
1                      vasu               40                     120                 380                   140

But when I tried to pivot the data it is showing as like this. It is  summing the result multiple times,

Empid        Name             2020                2021              2022                 2023
1                      vasu               160                  480                 1900                840

Here is the count for the record in each year

Empid        Name             2020                2021              2022                 2023
1                      vasu               4                         4                    5                        6

why is this counting multiple times in each year even the data has same or different amount in the records? how can i solve this issue? can i use some set analysis expression to resolve this?

[anat]

are you using only 1 tables as shown above or joining these table with other table?

[marcus_sommer]

Either there is an invalide linking between tables which lead to a kind of cartesian calculation in the object or the data are loaded multiple times. To see duplicated data you will need to add an unique record-id within your object (best would be a table-box and not a chart). If none such id exists you could create some with recno() and rowno() and you could track how many records are there now.