12 Replies Latest reply: Feb 25, 2017 12:15 AM by sumit jadhav

# How do I perform a sum of values for a unique field name within a field?

For example:

IncomeManaged Section 05
500Cats
600

Cats

784

Dogs

839

Cats

541Dogs

I want to know the SUM income of all field names "Cats" under "Managed Section 05"

• ###### Re: How do I perform a sum of values for a unique field name within a field?

sum( { < [Managed Section 05] = {"Cats"} > } Income)

It is called "Set Analysis". If you search on that, you will find a lot of information.

• ###### Re: How do I perform a sum of values for a unique field name within a field?

Fantastic. So I have a few follow-up questions/concerns:

I created:

Count ( { < Managed Section 05 ] = { 'Cats'} > } Income ) .... which worked fine only if I "inserted" income by using the add fields tool to the right. Also, for some reason it duplicated the values but I simply divided by 2. Why did it duplicate?

Now, instead of 'Income' I want to add a different field - 'ID' - for which I have to know the count of distinct Cat IDs (some repeat):

IDManaged Section 05
RS34

Cats

KA78

Dogs

ST56

Cats

HG49Dogs
TY87Cats
RS34Cats
TY87Cats
ST56Cats
GH65Dogs
MD12Dogs
KI90Cats

The following attempt didn't work:

Count ( { < Managed Section 05 ] = { 'Cats'} > } Count ( { Count ( { 1 } distinct ID ) )

Why? All the help is much appreciated!

• ###### Re: How do I perform a sum of values for a unique field name within a field?

create a pivot chart with expression

sum(income) and dimension as Managed Section 05

enable partial sum from object properties > presentation tab

• ###### Re: How do I perform a sum of values for a unique field name within a field?

Hi Ricky,

Sum( { < [Managed Section 05] = {‘Cats’} > } Income)

Regards,

Rajesh R. S.

• ###### Re: How do I perform a sum of values for a unique field name within a field?

Rajesh RS wrote:

Hi Ricky,

Sum( { < [Managed Section 05] = {‘Cats’} > } Income)

Regards,

Rajesh R. S.

Fantastic. So I have a few follow-up questions/concerns:

I created:

Count ( { < Managed Section 05 ] = { 'Cats'} > } Income ) .... which worked fine only if I "inserted" income by using the add fields tool to the right. Also, for some reason it duplicated the values but I simply divided by 2. Why did it duplicate?

Now, instead of 'Income' I want to add a different field - 'ID' - for which I have to know the count of distinct Cat IDs (some repeat):

IDManaged Section 05
RS34

Cats

KA78

Dogs

ST56

Cats

HG49Dogs
TY87Cats
RS34Cats
TY87Cats
ST56Cats
GH65Dogs
MD12Dogs
KI90Cats

The following attempt didn't work:

Count ( { < Managed Section 05 ] = { 'Cats'} > } Count ( { Count ( { 1 } distinct ID ) )

Why? All the help is much appreciated!

• ###### Re: How do I perform a sum of values for a unique field name within a field?

You can perform this by using set expressions.
Let me explain, Sum(income) is the expression we're working with. Now, if I want to just focus on some income, like in your case, juste sum(income) of the cats.

This is how we should proceed, go to same expression and edit it as follow:
1) the first step is to add {<>} : sum({<>}income) : this is where we're going to add our 'condition'

2) enter the condition : [Managed Section 05] = {‘Cats’}

a) Let's explain this: [Managed Section 05] is the field we're working with. We want to make selections in the field. In fact we want only to work with "Cats" => [Managed Section 05]= {  'Cats'  } (we allways open the {} to put a value in, if the value is numeric, don't use quotes , if it's a string like in our case, use the quotes; if it's an expression, please surround it with double quotes..)

3) Now, the expression has become like this : sum({<   [Managed Section 05] = {‘Cats’}  >}    income) : and this is how we read it : The sum of income for the managed section05 which has a value = Cats.

Omar,

• ###### Re: How do I perform a sum of values for a unique field name within a field?

Fantastic. So I have a few follow-up questions/concerns:

I created:

Count ( { < Managed Section 05 ] = { 'Cats'} > } Income ) .... which worked fine only if I "inserted" income by using the add fields tool to the right. Also, for some reason it duplicated the values but I simply divided by 2. Why did it duplicate?

Now, instead of 'Income' I want to add a different field - 'ID' - for which I have to know the count of distinct Cat IDs (some repeat):

IDManaged Section 05
RS34

Cats

KA78

Dogs

ST56

Cats

HG49Dogs
TY87Cats
RS34Cats
TY87Cats
ST56Cats
GH65Dogs
MD12Dogs
KI90Cats

The following attempt didn't work:

Count ( { < Managed Section 05 ] = { 'Cats'} > } Count ( { Count ( { 1 } distinct ID ) )

Why? All the help is much appreciated!

• ###### Re: How do I perform a sum of values for a unique field name within a field?

Now, that we have executed the first step; we want to be able to count the distinct id for the cats?
Here's how you perform it :
1) The basic expression is :  count(distinct ID)
2) Now we want to count the distinct ID of specific thing : cats

=> count(distinct {<[Managed Section 05]={'Cats'}>}  ID)
What this does is: count the distinct ID for the Cats.

If I want to count the distinct ID for every managed section (cats, dogs, or whatever)

Use a table:

- As dimension: [Managed Section 05]

- measure: count(distinct {<[Managed Section 05]=>}  ID)

=> which means, count distinct id for all the managed section

• ###### Re: How do I perform a sum of values for a unique field name within a field?

Thanks, works great.

• ###### Re: How do I perform a sum of values for a unique field name within a field?

How do I keep the number for this expression a static KPI within my dashboard?

• ###### Re: How do I perform a sum of values for a unique field name within a field?

count(distinct {<[Managed Section 05]={'Cats'}>}  ID) depends on your selections.

if want to have the count distinct id for all cats independant of your selections, you only have to add 1 :

count(distinct   {1<[Managed Section 05]={'Cats'}>}  ID)

• ###### Re: How do I perform a sum of values for a unique field name within a field?

Try this also,

Tab1:

[Managed Section 05]

FROM

[D:\Sumit\issue2.xlsx]

(ooxml, embedded labels, table is Sheet1);

if(WildMatch([Managed Section 05],'Cats'),[Managed Section 05]) as new

Resident Tab1;

DROP Table Tab1;

Output:

without using set analysis.