Hi there,
Trying to get my head around the feasibility of calculating new and existing items by a group based on the date ranges identifying the life span of each item.
My data looks like as follows:
| ClientId | ItemId | Group | CreatedDate | CancelledDate |
| 1 | 123 | A | 1/1/2019 | 3/15/2019 |
| 2 | 125 | A | 2/1/2019 | |
| 1 | 124 | A | 2/1/2019 | |
| 1 | 234 | B | 2/1/2019 | 2/15/2019 |
| 2 | 236 | B | 3/1/2019 | |
| 1 | 235 | B | 3/15/2019 | |
I've pivoted it to get the following table:
| M-YEAR | GROUP A | | GROUP B | |
| | New | Existing | New | Existing |
| 1-2019 | 1 | 1 | 0 | 0 |
| 2-2019 | 1 | 2 | 1 | 1 |
| 3-2019 | 0 | 2 | 2 | 2 |
| 4-2019 | 0 | 1 | 0 | 2 |
Now, new clients are clients who created at least one item in a group and do not have any active item from the same group (active item are items that were created before and is not canceled as of the year/month of the current row).
Existing clients are clients who have at least one active item in a group.
An ideal solution would accommodate for any number of groups, but I'm willing to start with this being limited to allow for some shortcuts.
The best I could come up so far is building my pivot table around CreateDate.YearMonth (as row) and Group (as column) dimensions to count distinct clients as new clients and rangesum / above new clients as existing ones. This provides an estimate but ignores important business logic:
a) For new clients, it counts a client as new each time they create at least one item in a group within the year/month of the current row (while it should exclude the ones who already created and not canceled items in the same group)
b) For existing clients, things get even worse as we just summing all new clients, whereas we should count clients who created and still not canceled at least one item in a group as of the year/month of the current row.
Essentially, I am looking to implement the following conditions for each cell of the pivot table:
New clients: Count distinct clients who have items with Group and CreatedDate that match the current cell and do not have items from the same Group that also have CreatedDate < min Date of the current cell AND CancelledDate > min Date of the current cell
Existing clients: Count distinct clients who have items with Group of the current column and CreatedDate <= max Date of the current cell and CancelledDate > max Date of the current cell.
-----
The task looks challenging enough for me to look for any solutions - from set expressions to changes to the data model and loading logic.
I will appreciate any advice and guidance.
