Re: Need to count active days
Accepted Solutions
@Ramesh I had to think about it for a bit, but I think I found a solution. I’m assuming you have a trips table that looks something like this:
Note that each driver and van is attached to each trip. Based on the above example table, Bob went on 3 trips on the 12th, and Bob, Sally, and Joe each went on a trip on the 11th. Bob also went on 1 trip on the 13th and 10th as well.
Thus, if you were to select the date range from May 11-May 12, you would want the following information:
Bob: 4
Sally: 1
Joe: 1
Van 1: 2
Van 2: 2
Van 3: 2
Note that overall, May 10th and May 13th should be left out if it works, meaning Van 1 overall is 2 less, and Bob is also 2 less.
The solution here is to use a Rollup instead of a Count. Here is an example:
Drivers
Vans
As you can see, the counts line up. The way this works is by using the toggle to allow only certain conditions to be included. Here is a screenshot for reference:
This should allow you to create fields with specific counts based on certain dates. Now, if you need each day to only show up as active or not active, then you need to change your aggregation formula so that it only selects unique dates. Here is a screenshot for reference:
Now Bob would have a count of 2 instead of 4, because he had trips on the 11th and 12th. It ignores the fact that he had multiple trips on the 12th. See below:
I hope this helps!
@Ramesh I had to think about it for a bit, but I think I found a solution. I’m assuming you have a trips table that looks something like this:
Note that each driver and van is attached to each trip. Based on the above example table, Bob went on 3 trips on the 12th, and Bob, Sally, and Joe each went on a trip on the 11th. Bob also went on 1 trip on the 13th and 10th as well.
Thus, if you were to select the date range from May 11-May 12, you would want the following information:
Bob: 4
Sally: 1
Joe: 1
Van 1: 2
Van 2: 2
Van 3: 2
Note that overall, May 10th and May 13th should be left out if it works, meaning Van 1 overall is 2 less, and Bob is also 2 less.
The solution here is to use a Rollup instead of a Count. Here is an example:
Drivers
Vans
As you can see, the counts line up. The way this works is by using the toggle to allow only certain conditions to be included. Here is a screenshot for reference:
This should allow you to create fields with specific counts based on certain dates. Now, if you need each day to only show up as active or not active, then you need to change your aggregation formula so that it only selects unique dates. Here is a screenshot for reference:
Now Bob would have a count of 2 instead of 4, because he had trips on the 11th and 12th. It ignores the fact that he had multiple trips on the 12th. See below:
I hope this helps!
