1 Reply Latest reply on Nov 20, 2014 10:22 AM by Joe Oppelt

    Count instances of record characteristics?

    mark bologna

      I have a data set we use for our daily operations management.  Each record (row) of data has multiple fields, but there is one field which contains tags (strings) separated by semi-colons.  I know how to write formulas to identify each tag, but I cannot seem to create a worksheet where I can count each of those separately (I know how to create one worksheet per tag, but I want them all on one sheet). I understand in doing this I may count a record twice, but as long as I can get a count of the instances of a particular tag I am ok with that.

       

      Attached is a very simple workbook.  In this workbook, I need to be able to count the total number of instances of Apple regardless of what other tags the record contains. Same for Bunny and Fuzzy.  Ideally, the dashboard would show both worksheets; one with counts and one with details.  When Apple was selected in the first sheet, an action would filter the second sheet so only records containing Apple would be displayed.

       

      Thanks,

      Mark

        • 1. Re: Count instances of record characteristics?
          Joe Oppelt

          Several ways to do this.

           

          I did a window_sum of [Apples] and another for [Bunny].  You can do the same for [Fuzzy].

           

          For the sake of demonstration, I just dumped them into the title of the sheet.


          See attached.


          That gets you the counts, but I'm not sure that's what you really want.


          I also added a parameter to let the user select one of the values.  Then I just filtered the expanded sheet using the same type of calc you used to count APPLEs.  User selects a value, and expanded sheet changes accordingly.