Subtotals Incorrect

[danm84306]

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.

[kmswetha]

Hi Dan,

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

[danm84306]

Hello,

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

[#4]

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

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

[danm84306]

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

[danm84306 · Accepted solution]

Hello,

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

[#7]

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:

LOAD * INLINE [

    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.