topic Re: Subtotals Incorrect in QlikView

Subtotals Incorrect

danm84306 — Thu, 23 Jul 2015 15:52:11 GMT

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

kmswetha — Thu, 23 Jul 2015 16:32:31 GMT

Hi Dan,

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

Re: Subtotals Incorrect

danm84306 — Thu, 23 Jul 2015 16:41:51 GMT

Hello,

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

Re: Subtotals Incorrect

Thu, 23 Jul 2015 17:43:06 GMT

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

danm84306 — Thu, 23 Jul 2015 17:58:52 GMT

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

danm84306 — Thu, 23 Jul 2015 18:08:18 GMT

Hello,

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

Re: Subtotals Incorrect

Thu, 23 Jul 2015 18:21:31 GMT

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.