Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
danm84306
New Contributor III

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.

1 Solution

Accepted Solutions
Highlighted
danm84306
New Contributor III

Re: Subtotals Incorrect

Hello,

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

View solution in original post

6 Replies
kmswetha
Contributor

Re: Subtotals Incorrect

Hi Dan,

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

danm84306
New Contributor III

Re: Subtotals Incorrect

Hello,

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

Not applicable

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.

danm84306
New Contributor III

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

Highlighted
danm84306
New Contributor III

Re: Subtotals Incorrect

Hello,

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

View solution in original post

Not applicable

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:

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.

23-07-2015 15-19-19.jpg

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

Best wishes.