5 Replies Latest reply: Jun 22, 2018 12:46 PM by Mohamed Elafifi RSS

    Count occurrences

    Mohamed Elafifi

      Hi All,

       

      I am trying to count the number of instances for each field.

      DummyData.PNG

      Please note: This is the script that I am using, and it is a master dimension. I can change the logic within the master dimension, but I cannot change the measures that I am using in the actual app. (I know that this is easily possible if I simply have a separate measure for each of the IDs)

       

      =if(WildMatch(IDs,'1,*')=1 or IDs = '1', 'FATIGUE',

      if(WildMatch(IDs,'*,2,*')=1 OR WildMatch(IDs,'*,2*')=1 OR IDs ='2', 'DISTRACTION',

          if(WildMatch(IDs,'*3,*')=1 or IDs = '3' or WildMatch(IDs,'*,3,*')=1, 'COMPLACENCY',

              if(WildMatch(IDs,'*4*')=1 , 'POOR TEAMWORK'

                ))))

        • Re: Count occurrences
          Mohamed Elafifi

          stalwar1, sorry I wasn't able to attach a document and image in our message, so this was the option I had to proceed with. Any help is greatly appreciated.

           

          Mohamed

            • Re: Count occurrences
              Sunny Talwar

              Try this

               

              Dimension

              =ValueList('FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK')

               

              Expression

              Pick(Match(ValueList('FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'), 'FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'),

              Sum(Aggr(NODISTINCT SubStringCount(IDs, 1), IDs)),

              Sum(Aggr(NODISTINCT SubStringCount(IDs, 2), IDs)),

              Sum(Aggr(NODISTINCT SubStringCount(IDs, 3), IDs)),

              Sum(Aggr(NODISTINCT SubStringCount(IDs, 4), IDs)))


              Capture.PNG

                • Re: Count occurrences
                  Mohamed Elafifi

                  Hmm, I guess I should have been more thorough with my example. Please see the attached qvf. There are two distinct groups that I am counting for. When implementing for one group, yours worked well on the larger data set that I have, but when implemented for two groups, I get no visualization, unfortunately.

                    • Re: Count occurrences
                      Sunny Talwar

                      Try this

                       

                      Pick(Match(ValueList('FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'), 'FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'),

                      Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'Q'}>}IDs), 1), IDs)),

                      Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'Q'}>}IDs), 2), IDs)),

                      Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'Q'}>}IDs), 3), IDs)),

                      Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'Q'}>}IDs), 4), IDs)))


                      Pick(Match(ValueList('FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'), 'FATIGUE', 'DISTRACTION', 'COMPLACENCY', 'POOR TEAMWORK'),

                      Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'W'}>}IDs), 1), IDs)),

                      Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'W'}>}IDs), 2), IDs)),

                      Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'W'}>}IDs), 3), IDs)),

                      Sum(Aggr(NODISTINCT SubStringCount(Only({<Workmanship = {'W'}>}IDs), 4), IDs)))


                      Capture.PNG

                        • Re: Count occurrences
                          Mohamed Elafifi

                          This didn't quite give me the correct response, as it didn't scale to the number of ID's that I have in the actual app (17), so I made some tweaks and it seemed to work properly. I used a sum(wildmatch(),1), but still used the pick(match(valuelist())). Thank you very much.