2 Replies Latest reply on Jun 8, 2016 8:24 AM by Steve Wilson

    Count - Calculated Fields

    Steve Wilson

      Afternoon Everyone,

       

      I am having a little trouble creating a calculated field that counts the frequency of a measure with respect to another measure.

      Below is a sample of my data set:

       

      IDRunTool(Count_Tool - Calculated Field)
      324561          A3
      324562          B
      324563          C
      324571          A3
      324572          A
      32457  3          B
      324574          C
      324581          B1
      324591          A

      2

      324592          B
      324593          B
      324594          B

       

      I am trying to count how many tool types were used for each ID.

       

      Example, for ID 32456 there were 3 separate runs (1,2,3) each using a different tool type A, B  or C - in this case the count would return 3.

       

      For ID 32459 there were 4 separate runs (1,2,3,4) but only two tool types were used A and B - in this case the count would return 2.

       

      I am not sure if it helps but, I wrote a python script to do this (shown below).

       

      ID = []

      Tool = []

      IDnTool = defaultdict(list)

      print 'ID -',ID

      print 'Tool -',Tool

       

      for x in range(len(ID)):                   ##Creates a dict adding each ID and all respective tools used

          IDnTool[ID[x]].append(Tool[x])

      print IDnTool

       

      IDnFreq = {}

      for key in IDnTool:                               ##Runs through keys adding  'new' tools to list

          Checker = []                                    ## then checks how many tools present for each ID

          for value in IDnTool[key]:

              if value not in Checker:

                  Checker.append(value)

          IDnFreq[key] = len(Checker)

      print IDnFreq

       

      All advice would be greatly appreciated

      Thanks
      Steve

        • 1. Re: Count - Calculated Fields
          Simon Runc

          hi Steve,

           

          A few ways of going about this...one would be in the Viz

           

          So drag ID in your Viz, and then drag COUNTD([Tool]) into the measure canvas....however this will only calculate correctly when you have ID in your VizLoD (Viz Level of Detail) as the COUNTD is being run at the level of ID.

           

          To get this to work 'off canvas' so you get these figures in the data, and so can use as dimension, we can use a FIXED LoD (so you could then use in histogram from example)

          [Count of Tools per ID]

          {FIXED [ID]: COUNTD([Tool])}

           

          Hopefully that does the trick, and makes sense, but if not please post back (and I can point you at some great LoD resources, or have another go if that doesn't work for you)

          1 of 1 people found this helpful
          • 2. Re: Count - Calculated Fields
            Steve Wilson

            Thanks Simon, solved the problem and histogram is done