Hi all!
Can't give out the real data but let's use a paradigm to describe my question.
The data is like this: An ID can have multiple orders. Each order has an associated field status like (check, recheck, sent), reply (yes, no). However, this ID can appear again in multiple years. So (example):
ID status Year statusID reply
1 check 2019 1 yes
1 recheck 2019 2 yes
1 check 2020 3 no
2 sent 2021 4 yes
I am trying to count all the IDs that have at least one 'yes' per last status category . I did:
count({<[last reply for check]={'yes'}>+<[last reply for recheck]={'yes'}>+<[last reply for sent]={'yes'}>} distinct [ID])
but for example if i pick ID 1, i want it to calculate 0 , but instead because of this ID has a last reply for recheck with a yes , it will count 1 . There isn't a distinction by the years.
How to fix this? How to take into account the years?
Back in script i calculated the last result by ID,status and year.