Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.