occurunce
1 Topic
Counting instances
Hi there I am trying to count the instances of Covid over a period of time. There are two piece to this. 1.) how do I count the instances where the first instance would be labelled 1 and the second as 2, third as 3 and so on. Using earlier seems to mark them all as the highest count. so the first second and third would all show a count of 3. 2.) I'd like to do this to see if the number of multiple instances has changed year on year. So count the first one in the year as 1, second as 2 then the next year restart the count at 1, and so on. My data is laid out as follows From Date UNID 19/03/2020 F005F 15/11/2020 F0040 20/01/2021 Y8F44 01/03/2021 F00FF 04/03/2021 F0078 09/06/2021 Y8F08 30/06/2021 F005F 20/07/2021 a0588 30/07/2021 F00FF 14/09/2021 F00FF 25/09/2021 F00FF 19/11/2021 F0090 12/12/2021 Y8F44 03/01/2022 a058F 23/05/2022 a0584 01/06/2022 a0588 05/07/2022 F00FF 11/07/2022 F0078 I have a caelndar (simple just every day of the year) joined on from date 1-Many. This would be my expected result. expected result Total Count In Year Count From Date UNID 19/03/2020 F005F 1 1 15/11/2020 F0040 1 1 20/01/2021 Y8F44 1 1 01/03/2021 F00FF 1 1 04/03/2021 F0078 1 1 09/06/2021 Y8F08 1 1 30/06/2021 F005F 2 1 20/07/2021 a0588 1 1 30/07/2021 F00FF 2 2 14/09/2021 F00FF 3 3 25/09/2021 F00FF 4 4 19/11/2021 F0090 1 1 12/12/2021 Y8F44 2 2 03/01/2022 a058F 1 1 23/05/2022 a0584 1 1 01/06/2022 a0588 2 1 05/07/2022 F00FF 5 1 11/07/2022 F0078 2 1 From this I would then like to be able to count the number of people with multiple instances in each year and the average number of instances - which should be pretty straight forward if this is a calculated column.Solved2.6KViews0likes4Comments