9 Replies Latest reply on Feb 10, 2017 9:10 AM by Jim Thompson

    Want to create a calculated field that "scans/counts" over all data (v10.1.4)

    Jim Thompson

      Hey folks-- so, I've got a mental challenge.. mental block actually...  I've got a hierarchy field in a sales forecast sheet.  Each salesman has an ID, and an associated hierarchy of those above him... My challenge is to figure out if there is anyone BELOW him...

       

      For example, here are a couple of rows from the packaged workbook v10.1.4:

      YearShort NameIDHierarchyOffering IDCompany NameForecast
      2017Fred12345/12345/1001Secret Submarines Inc$2,000,000.00
      2017John45612/45612/1002Spies R Us$500,000.00
      2017Ralph78945/12345/78945/1003Radio Shack$80.36
      2017George46465/12345/46465/1004Satellite Company$7,000,000.00
      2017Evie71714/45612/71714/1005Black Get-a-way Cars$250,000.00

       

      The question is, how can I make a calculated field that indicates that for a given ID (not Offering ID) he's/she's a manager also (ie, their ID occurs somewhere else in the hierarchy data field (in other rows), but doesn't end with it)?

       

      So for example, for Fred, (ID = 12345) isManager would be True as Ralph and George (as shown) report to him (have Fred in their hierarchy).

       

      I've tried a LOD... crash and burn... I'm wondering if I don't need to duplicate my data source and reconnect as a secondary or some such magic!

       

      Any help here would be great!!

      Thanks,

      jim

        • 1. Re: Want to create a calculated field that "scans/counts" over all data (v10.1.4)
          Khang Pho

          Hi Jim,

           

          I'm not sure if it is just your sample data but is it true to say that if the first value in their Hierarchy is the same as their ID then they are a manager?  If that is the case then have you tried doing a text match on just those characters?  Something like the following:

           

          IF MID([Hierarchy],2,5) = [ID] THEN 'MANAGER' END

           

          You would have to adjust the formula if ID isn't always 5 characters.

          • 2. Re: Want to create a calculated field that "scans/counts" over all data (v10.1.4)
            Jim Thompson

            So, I'm afraid not (but you raise a great point that I might not have illustrated enough data from the Excel embedded in the packaged workbook).  So from below, Janice is a manager of Ralph (the 44444 Ralph) but she has managers above her and her hierarchy starts with /45612/! 

             

                     

            YearShort NameIDHierarchyOffering IDCompany NameForecast
            2017Fred12345/12345/1001Secret Submarines Inc$2,000,000.00
            2017John45612/45612/1002Spies R Us$500,000.00
            2017Ralph78945/12345/78945/1003Radio Shack$80.36
            2017George46465/12345/46465/1004Satellite Company$7,000,000.00
            2017Evie71714/45612/71714/1005Black Get-a-way Cars$250,000.00
            2017John45612/45612/1006Ski Masks, LLC$287.66
            2017Lucille89891/12345/89891/1007Burner Phones Inc$800.00
            2017Evie71714/45612/71714/1008Speedy Passports$1,500.00
            2017Karen33333/45612/33333/1010A-Z False Moustaches, Inc$500.00
            2017Janice77777/45612/71714/77777/1012Safe House Realty$79,000.00
            2017Lucille89891/12345/89891/1013Poison Pens$990.00
            2017Mike79797/45612/71714/79797/1014Ron's Switch Blades$264.00
            2017Ralph44444/45612/71714/77777/44444/1015EZ Body Disposal$79.95
            • 3. Re: Want to create a calculated field that "scans/counts" over all data (v10.1.4)
              David Li

              Hi Jim, this is an interesting problem! I think I've got it, but unfortunately, you'll have to use table calculations, which will limit your choices when building sheets. You can't use LOD calculations here because you can't really use LOD calcs to "scan". That is, the dimensionality part of an LOD calc evaluates itself and groups up all records into matches, so you can't, for instance, take the Hierarchy from one record and search through the other records to see if that Hierarchy is included in them.

              Basically, if we sort by Hierarchy, we can use LOOKUP() to see if the next person's hierarchy contains the hierarchy of the current person. If so, then the person is a manager. Here's the formula:

              FIND(LOOKUP(ATTR([Hierarchy]), 1), ATTR([Hierarchy])) = 1

              This needs to be computed using all the dimensions, and it needs to use At-the-level to work properly if a person occupies multiple rows. The At-the-level should be set to whatever dimension creates duplicate rows per person--in this case, Offering ID.

              • 4. Re: Want to create a calculated field that "scans/counts" over all data (v10.1.4)
                Jim Thompson

                David- Wow! Great thinking... certainly an approach I had not considered... but... your solution requires that the "children" be present in the View, which, is fine for this sample data, but won't work for the full scale 8,000 sales reps in the organization as the View would be gnarly!  And, as illustrated, if we filter out Ralph (77777) suddenly Janice (and understandably) goes to FALSE from True... I kinda need her to remain True...

                 

                Thanks!!

                • 5. Re: Want to create a calculated field that "scans/counts" over all data (v10.1.4)
                  Joe Oppelt

                  If your filter is a table calc, then Janice will stay in the underlying table, even if she is filtered from the view.

                   

                  Create a calc that looks like this:

                   

                  LOOKUP(attr([short name]) , 0)

                   

                  Put that on filters shelf.  Show filter.  Filter people in-and-out.

                   

                  The underlying table will still remain intact.  If you had a calc something like this:

                   

                  WINDOW_SUM(COUNT([ short name]))

                   

                  ...and displayed that in the title, for example, it would always remain constant, even if your LOOKUP calc filters people out.

                  • 6. Re: Want to create a calculated field that "scans/counts" over all data (v10.1.4)
                    Jim Thompson

                    Great point (understood regarding the order of filtering priorities) but the if we look at the output, Lucille's "is she a manager" field is set to both True in one instance and False in the next since it finds a second row (a second Sales offering in the pipeline) for her... It's not really a lookup as much as its a Lookup with the need for a REG_EXP ==> /ID/[0-9]+/ ensuring there's another "node" after hers (or in addition to hers)....

                     

                    On a positive note, I don't feel too badly about posting this- I really appreciate the great ideas so far!

                     

                    Jim!

                    • 7. Re: Want to create a calculated field that "scans/counts" over all data (v10.1.4)
                      Joe Oppelt

                      I would look for a way to reshape this data.  Have separate hierarchy fields. 

                       

                      Everyone reports up to the president.  He would be your level-1 guy for all other employees (and maybe for himself.)  Maybe 15 people report to him.  They would be level-2 names (maybe just for themselves, if they are individual contributors, else all 1000 other employees would have one of these level-2 guys in their level-2 column.)  And level-3, 4,5,6 etc., depending on how deep your org chart goes.

                       

                      You don't actually have to store all these levels in the data source in individual columns, now that I think about it.  Calcs could pick apart the hierarchy field to populate their own level value.  There is a FINDNTH() string function that will let you find the Nth occurrence of a string.  if you have those slashes in there, that makes FINDNTH() easy to use.

                       

                      So you build out a grid of calcs that have either an actual value, or null if the hierarchy string doesn't go out that far for a given employee.

                       

                      Then, if you set it up right, you can have a LOD at each level look at all the rows below it.  Something like this:

                       

                      if [Level 2] <> [ID] then  // If this row's level-2 is its own ID, then this is the level-2 boss of the level-3 rows that have him

                      { fixed [Level 2] : count(if [Level 3] = [ID] then [Employee Name] end) }

                      end

                       

                      What this does is takes all the chunk of rows for each value in level-2, and looks at the level-3 value in those rows, and if the level-3 for any of those rows equals the [ID] for that row, then it's the end of the hierarchy in that row, and the level-2 guy is his boss.  The sum of all those rows that meets that condition is the number of direct reports to that level-2 guy.  And therefore, if that level-2 guy has a sum greater than 0, he's a boss, and you even know how many directs he has.


                      Just thinking out loud.  And maybe the syntax needs tweaking, but I can see this working out, and it's one way to look at this sort of data.

                      1 of 1 people found this helpful
                      • 8. Re: Want to create a calculated field that "scans/counts" over all data (v10.1.4)
                        Jim Thompson

                        Thanks Joe- ok, so my instinct on the calculated field in the attached packaged book wasn't so "crash & burn" as just not smart enough!! Lemme mull over your suggestion and see if that can get me where I need to go!

                         

                        Thanks again!!

                        Jim!

                        • 9. Re: Want to create a calculated field that "scans/counts" over all data (v10.1.4)
                          Jim Thompson

                          Joe-- you are the Master! I tweaked your logic a touch, but essentially you were right on! I believe your COMMENT is accurate but the formula was backwards-- I do this all the time so I can relate!! Just need to change the <> to =

                           

                          Rather than:

                          if [Level 2] <> [ID] then  // If this row's level-2 is its own ID, then this is the level-2 boss of the level-3 rows that have him

                          { fixed [Level 2] : count(if [Level 3] = [ID] then [Employee Name] end) }

                          end

                           

                          Becomes

                           

                          if [Level 2] = [ID] then  // If this row's level-2 is its own ID, then this is the level-2 boss of the level-3 rows that have him

                          { fixed [Level 2] : count(if [Level 3] = [ID] then [Employee Name] end) }

                          end

                           

                          And this yields the desired outcome (this particular calc field I named [Level2 Manager Count] (because that's what it is...duh).  I then build one for all the other levels, making the simple changes and then created an [isManager] that was simply IF [Level1 Manager Count] > 0 OR [Level2 Manager Count] >0 OR [Level3 Manager Count] > 0 ...etc THEN 'True' Else 'False' END and VOILA!

                           

                          This is one of those examples of LOD that should be presented at the next Conference! (You should present!)

                           

                          Thanks again!

                          Jim!