How to do a count distinct by a certain value

[#1]

I need to setup count distinct syntax in a variable but it has to be counted by another field. Here is the syntax I have but it is not working. Any help is appreciated, thank you!

count(distinct ID) for AFD

[Qrishna]

could you please explain clearly what you are trying to achieve?

Thanks

[#3]

Hi Lindsay

If I have understood correctly I am thinking that you wish to be able to determine the number of distinct entries of values held in another field.

There are a few ways to achieve this, both in script or dynamically.

By means of example let's say that you have the following data:

My assumption from your 'count distinct syntax in a variable but it has to be counted by another field' is that you want to know how many distinct ID's there are per Item.  So for example in the data above we can see that there are two distinct ID's for Item 'One'.

In the script:

You can create the count of distinct ID's per item in the script using the following code:

Load
Item,
Count(Distinct ID) as countOfDistinctIDs
Resident SampleData
Group By Item
;

In the above script the 'Group By' function is used to organise the data by the field 'Item' so that distinct ID's against each 'Item' can be counted.

This will liberate a result set of:

The disadvantage if this method is that it is not dynamic and so it may not play well with any selections you make in your presentation application.

In the application:

There are ways you can create this result dynamically.  For example, if you wish to replicate the result set created by the script above but in a dynamic format that will work with your selections then you can use a straight table

Here the dimension is Item and the expression is Count(Distinct ID)

Alternatively, let's say you wanted to only find out the distinct count of ID's where the Item is 'Two'.  We know in this example the answer should be 9.  You can use set analysis to make this kind of dynamic selection and can set the result as a variable if you wish for example to display the result in a text box.

The set analysis statement for this would be: =Count({$<Item={'Two'}>}Distinct ID)  which is actually saying 'Count the number of distinct ID's where the Item is 'Two'.  If we placed the formula in a text box this would give the result 9 which we know to be correct.

If you were to put this in the variable overview (on the settings menu) you could create it as a variable.  For example, if you created a new variable called vCountDistinctIDforItemTwo then placing the set expression above as the definition

of this variable would allow you to call it in a text box using =vCountOfDistinctIDforItemTwo.

You could even make it dynamic so you can choose which item you want to use e.g. if you create a variable called vCountOfDistinctIDforItem and set its definition as the set expression =Count({$<Item={"$(=GetFieldSelections(Item))"}>}Distinct ID)

and use the =vCountOfDistinctIDforItem in a text box then this would update the text box with the count of distinct ID's for whichever single Item you select in a list box.

Hopefully somewhere in this response I have managed to help you with your issue.

Kind regards

Steve