2 Replies Latest reply on Jul 20, 2018 1:25 PM by Mary Sorensen

    Mosquito counts: how to sum over a variable number of dates?

    Mary Sorensen

      Hello!  We have a device that automatically counts mosquitoes, and produces a number for each night.  Periodically, we physically collect mosquitoes out of the device and count them to check accuracy.  The manual counts include a variable number of nights (one to seven).  I need to sum the correct automatic counts to compare the total to the manual count.  I am including a packaged workbook with sample data.  I used Tableau prep to get the data to its current state, so if not a calculation in Tableau itself, I could potentially also do some kind of calculation in Tableau Prep?  Any ideas would be so very much appreciated!  Tableau 10.5 and Tableau Prep 2018.1.1.

       

        • 1. Re: Mosquito counts: how to sum over a variable number of dates?
          Aaron Sheldon

          Your existing data set is very close, you just need the "day of collection for manual count" to be propagated over all the nights that are covered by the manual collection, not just the first night. This is best done as part of the shaping step in Tableau Prep. Without knowing the original source data I can guess at the following procedure:

           

          1. Left join your data set containing one record per night of automatic capture [AUTOMATIC] to your data set containing one record per day of manual collection [MANUAL], on the condition [AUTOMATIC].[NIGHT] <= [MANUAL].[DAY] AND [MANUAL].[DAY] - [MANUAL].[DAYS] + 1 <= [AUTOMATIC].[NIGHT] AND [MANUAL].[TRAP] = [AUTOMATIC].[TRAP].
          2. Ensure all the nightly records are included, and only the matching manual records are included.
          3. The day of manual collection [MANUAL].[DAY] should be available for each record covered by the same manual collection
          4. I have assumed the [AUTOMATIC].[NIGHT] contains the day of automatic capture, [MANUAL].[DAY] contains the day of manual capture, and [MANUAL].[DAYS] contains the previous days covered by manual capture, including the current day.
          • 2. Re: Mosquito counts: how to sum over a variable number of dates?
            Mary Sorensen

            It worked!  This is the first time I have posted on the forum and I really appreciate the awesome community here.  Thanks for getting me un-stuck!