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

# Count - Calculated Fields

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:

 ID Run Tool (Count_Tool - Calculated Field) 32456 1 A 3 32456 2 B 32456 3 C 32457 1 A 3 32457 2 A 32457 3 B 32457 4 C 32458 1 B 1 32459 1 A 2 32459 2 B 32459 3 B 32459 4 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

hi Steve,

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

Thanks Simon, solved the problem and histogram is done