6 Replies Latest reply: Jul 23, 2015 2:21 PM by Marco Rotta

# Subtotals Incorrect

Hello,

I am having difficulty with a subtotal. My expression is a simple count of date as it relates to a person. For example, data with dates 7/20, 7/21, 7/22, 7/23, and 7/24 under my name would register as 5 days of entered data. My calculation is count(DISTINCT{<Name>}Date) which returns the correct individual results but the subtotal is not correct. I want the subtotal to be all names and distinct dates sum. The data is in a pivot table.

• ###### Re: Subtotals Incorrect

Hi Dan,

You have to change the expression total to sum of rows.

• ###### Re: Subtotals Incorrect

Hello,

This is not an option, All grey. I do not have a dimension limits tab as well.

• ###### Re: Subtotals Incorrect

Doesn't a simple count(DISTINCT Date) return what you need? Why?

PS.: I'm assuming the "Name" field is being used as dimension.

• ###### Re: Subtotals Incorrect

Yes, Name is the Dimension. The individual calculation works but the sum total does not, it will only count the individual dates. I need sum total of date per name

• ###### Re: Subtotals Incorrect

Now I get the scenario. The answer you've just found is one of the possible. You can solve it with AGGR() too.

I've loaded the following test data:

Data:

Name, Date

John, 01/01/2015

John, 02/01/2015

John, 04/01/2015

Mary, 01/01/2015

Mary , 05/01/2015

Paul, 02/01/2015

Paul, 06/01/2015

Paul, 07/01/2015

];

Below there are three expressions/columns:

1. The first one is your problem scenario, if I got it right.

2. The second one is your solution, counting couples of "name-date".

3. The third is a solution using AGGR(). Note the Sum used over the AGGR count.

My answer came a little late, but I still wanted to mention the AGGR alternative.

Best wishes.

• ###### Re: Subtotals Incorrect

Hello,

The answer to this issue is Count(Distinct Date&Name).