Duplicates

[Krish2459_58]

Hi,

I have data like this and when we do the calculation like below it ends up with duplicates.

if(num([cal date])-num(today())>0 and confirmed='N',1,0) as "Review Eve"

expression: sum("Review Eve") gives duplicates

ref	cal date	major code	minor code	review	quoted amt	agreed amt	confirmed	today()
00ACCL27	01-04-2016	200	100	Initial Rent		177947.88	Y	09-05-2024
00ACCL27	01-04-2016	200	130	Initial Rent		14351.93	Y	09-05-2024
00ACCL27	01-01-2017	200	100	Rent Review		177948	Y	09-05-2024
00ACCL27	01-01-2017	200	130	Rent Review		14352	Y	09-05-2024
00ACCL27	01-04-2019	200	100	Rent Review	177947.88	177947.88	Y	09-05-2024
00ACCL27	01-04-2019	200	130	Rent Review	14351.93	14351.93	Y	09-05-2024
00ACCL27	01-04-2022	200	100	Rent Review	177947.88	177947.88	Y	09-05-2024
00ACCL27	01-04-2022	200	130	Rent Review	14351.93	14351.93	Y	09-05-2024
00ACCL27	01-04-2025	200	100	Rent Review	177947.88	0	N	09-05-2024
00ACCL27	01-04-2025	200	130	Rent Review	14351.93	0	N	09-05-2024
00ACCL27	01-04-2028	200	100	Rent Review	177947.88	0	N	09-05-2024
00ACCL27	01-04-2028	200	130	Rent Review	14351.93	0	N

09-05-2024

[mpc]

Hi, 

Can you check the date format of "cal date" ? If it's a not a date but a timestamp, you'll need to floor of ceil it to have only the integer part of the number, and then a true date. It will remove duplicated date. 

Kind regards

[Krish2459_58]

It is a date format without time stamp. The duplicates is beacuse of cal date and when put sum(distinct "Review Eve") It gives only 1.