Forum Discussion
Returning values based on Start Date / End Date in a given year
Hi peteryac60
Thank you so much for this! This is a much better way of doing it 🙂
Basically I want to COUNT the number of Agents in 2017 that had ACTIVE contracts. For this, I need to know the contract type and and status in a given year. We are so close now!
So my end result would be below (using dummy numbers). So in 2017 I don;t want to count Agents that used to be ECs in 2017. In 2017 they should be under 'EC'. So really the 16 should be 0, and EC 46 (because they were not agents in 2017). Ignore the %s for now.
Active Number Of ECs & Agents :
| 2017 | 2018 | 2019 | 2020 | ||||||
| Agent | 16 | 15% | 29 | 27% | 43 | 40% | 52 | 48% | |
| EC | 40 | 69% | 41 | 71% | 42 | 72% | 50 | 86% |
Hi Jenny,
Sorry you have confused me a bit further!!
Is this what you are looking for?
If not let me know and I can look again!
regards,
Peter
Hi Peterpeteryac60
I am confusing myself too! Ha ha.
So I need to count all contracts in 2017 that were Active and ECs.
The tricky part is determine which were active at the time and which were ECs at the time. We can't use the Contract Type and Status Fields, as they are as of today. But an Agent today, could have been an EC in 2017. And an expired contract today, could have been live in 2017.
I need counts in one table, but I also need a Contract Type by year field and a Status by Year field in the back end. For example:
Q1: In 2017 how many ECs had Active contracts where:
ECs: Contract Type = EC (if client has 4 contract types, we need to pick the one relating to 2017, which can only be determined by the start and end date)
Active contracts: Status = Active ( issue is that we need to know contract status during a given year, currently shows today’s status), so the Status may = expired where the start and end date cover 2017, but we need it to say 'Active' to change to what it was at the time.
Sorry if i'm not making sense!
Jenny - here we go again !
I have adjusted the count formula which might be what you want?
I have also attach a new sheet (PIVOT) with a couple of simple pivot tables which give the same information. In the pivot table you can select from the from the drop down (e.g. contract type) to get other information.
BTW - a suggestion (you can ignore!) ; in your data you maybe should have the Status 2017 next to Contract Type 2017; then the 2018 columns next to each other and so on!
Peter
Hi peteryac60
Oh I am open to suggestions, so please fire away 🙂
Only issue now is that it s not counting unique clients. A client name can be repeated several times, but we only want to count once in each year...see the fun I'm having? 🙂 But I really want to crack this, will solve so many problems!