Hi All, I have a data sets of serial numbers and activities. I want to determine the number of active serial numbers in a monthly view. An active serial number would be the one with atleast one activity in the last 3 months. I am using start and end date to build a date on the view using activity date. I am unable to get actual number of serial numbers that had activities in the last 3 months on a monthly basis. The report should count how many serial numbers were active from the month on the view going back 3 months. Using my dataset attached with the worksheet, my desired results should be
April : 2
May: 2
June: 1
July: 2
Cheers @Michael Madisha
If you didn't want to sum the numbers up, and always had each month and serial# in the view then a table calc might do the trick, but it's a bit complex ... especially if you want to sum the numbers up!
Jim's suggestion of a scaffold is an easier way to go. Here is an example:
Extra sheet/table for all the months you want to show:
Then you relate to your data something like this:
The idea here is that every month will have the rows that happen in that month and either of the two months prior (for the 3 month period).
You can build a test view to check this is working:
So for March I see activity from March, February and January.
Then we can simply count up distinct serial numbers:
Well, I say "simply" as I do get different numbers from you but perhasp I've misunderstood the logic and not quite got it right! Eitherway hopefully this gives you a pretty good jump start.
Let me know!
Workbook attached.
Ta, Steve.
