1 2 Previous Next 27 Replies Latest reply on Sep 21, 2018 8:11 AM by Shinichiro Murakami Go to original post
      • 15. Re: Calc help
        John Hobby

        So, I made a new calculation in your workbook called 'Total Monitors per LDAP' with the following formula:

        {fixed [LDAP Name]

            : countd(

                IF CONTAINS([Call ID or Customer Email], '/') THEN NULL

                ELSE [Call ID or Customer Email] END)

        }

         

        This will count the number of UNIQUE or DISTINCT call id or customer email for each LDAP.  The CONTAINS piece is an attempt to exclude any call id or customer emails that have the ' / ' or forward slash in the field.  If it finds one, it returns a NULL, so that doesn't get counted in the distinct count formula.  for the 'JG", this returns ' 0 ' ... as the only value in the email field is the n/a.   for the 'TC' it returns a ' 2 ', because there are 2 distinct values for the email ( 1 and 5 ).  This doesn't account for the fact the total number of records in the dataset (seeing as how the TC ldap name has 14 records in total)

         

        Does this help or am I still missing something? 

         

        John

        • 16. Re: Calc help
          John Hobby

          Ha - i posted my last comment before i saw this one 

           

          This should give you what you are asking for:  It is the total number of UNIQUE entries in that email field for that LDAP.  If you use this with the TC ldap name, it will return '2' because there are 2 unique entries for that name (1 and 5).

          { fixed [LDAP Name] : countd([Call ID or Customer Email]) }

          • 17. Re: Calc help
            Barb Reiser

            I get 0's on the first 3 when I use this calc, which are the ones with the NA's....

             

            {fixed [LDAP Name]

                : countd(

                    IF CONTAINS([Call ID or Customer Email], '/') THEN NULL

                    ELSE [Call ID or Customer Email] END)

            }

             

             

             

            • 18. Re: Calc help
              Barb Reiser

              This one doesn't work either because it's doing a countd on the call id. { fixed [LDAP Name] : countd([Call ID or Customer Email]) }

               

              If I have multiple NA's then it's not going to give me the correct total.

              • 19. Re: Calc help
                John Hobby

                Change it to:

                {fixed [LDAP Name]

                    : countd([Call ID or Customer Email])

                }

                • 20. Re: Calc help
                  John Hobby

                  so, if there are multiple n/a values - are you needing to count each n/a separately while only counting the actual value only once?

                  • 21. Re: Calc help
                    Barb Reiser

                    So when you look at the raw data, I need to count each one of these. JG and TC are in here twice, so they should say 2 where all the rest should say 1.

                     

                    • 22. Re: Calc help
                      John Hobby

                      Gotcha ... so, someone may be able to write this better   but think this will give you the answer now

                       

                      // this evaluates for each way that the 'N/A' can be entered.  You may need to add an extra line in here if there is another version of 'N/A'

                      {fixed [LDAP Name] : countd(IF [Call ID or Customer Email] = 'n/a' then 1 end)}

                      + {fixed [LDAP Name] : countd(IF [Call ID or Customer Email] = 'N/A' then 1 end)}

                      + {fixed [LDAP Name] : countd(IF [Call ID or Customer Email] <> 'N/A'

                      OR [Call ID or Customer Email] <> 'n/a' then [Call ID or Customer Email] end) }

                      • 23. Re: Calc help
                        Barb Reiser

                        I uploaded another workbook, book3. What if I had to data blend, to get the correct total. Is there a way for me to fix the view so it's not showing two rows for TC?

                         

                        • 24. Re: Calc help
                          Shinichiro Murakami

                          For this case,

                           

                          Cannot guarantee 100% of case shows correct value though.

                          Shin

                          • 25. Re: Calc help
                            Barb Reiser

                            Hi Shin,

                             

                            I'm marking that one as correct. It seemed to work from the spot checks I did. There was one person that showed up as Null and I'm not sure why as they had 4 monitors. When I copied the data over to my test book for that person, it appeared correctly there, just not in my master workbook. So I'm going to investigate more.

                             

                            Appreciate your time on this!

                             

                            Barb

                            • 26. Re: Calc help
                              Barb Reiser

                              Hey Shin,

                               

                              Just to update you, I did end up getting the Null resolved. I just had to go into the edit relationships and modify the date options and also select the link for LOB.

                               

                              Thanks again!

                               

                              Barb

                              • 27. Re: Calc help
                                Shinichiro Murakami

                                Sounds good.

                                 

                                Shin

                                1 2 Previous Next