Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Dan,
You have to change the expression total to sum of rows.
Hello,
This is not an option, All grey. I do not have a dimension limits tab as well.
Doesn't a simple count(DISTINCT Date) return what you need? Why?
PS.: I'm assuming the "Name" field is being used as dimension.
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
Hello,
The answer to this issue is Count(Distinct Date&Name).
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.