topic Count total in QlikView

Count total

Fri, 04 Dec 2015 14:30:05 GMT

I am trying to do a table with two expressions but without success. I am having issues with the last expression taht would be a total

The table is a pivot table with one dimension for the Question and another one with the answer.

The two expressions are the number of answers and the total number of answers at a question level.

Question	Answer	Count (Distinct Answer) aggregated per Survey	Count total
1	1	74	698
1	2	73	698
1	3	408	698
1	4	90	698
1	5	53	698

The first Expresion is correct, and it is displaying the number of distinct answers for each survey:

sum(aggr(count(distinct Answer),Survey, Answer)).

However, when I try to do the total count at a question level I am able to obtain the total if I do not select any Answer with the following calculus

sum( total <Question, Survey,> {<Answer>} aggr(count( {<Answer>}Answer),Survey, Answer)))

But when I select an Answer, instead of displaying the total at a Question level, it displays the same figure than the number of answers for the answer, and not the total answers for the answers in the question: 698

Question	Answer	Count (Distinct Answer) aggregated per Survey	Count total
1	1	74	74

Thank you.

Best regards.

Re: Count total

swuehl — Fri, 04 Dec 2015 17:25:14 GMT

I could be totally wrong here.

Wouldn't your first expression be equivalent to

=Count(DISTINCT Survey)

And then your total maybe

=SUM( Aggr( Count(DISTINCT Survey), Question, Answer))

resp. to ignore selection on Answer:

=SUM({<Answer= >} Aggr( Count({<Answer= >} DISTINCT Survey), Question, Answer))

Re: Count total

Mon, 07 Dec 2015 08:26:17 GMT

Thank you, but it does not work:

The calculus is aggregate count of answers at a survey and answer level, as one survey-question could have more than two answers.

When I put your formula the output is the same, as it is not being done at a total level.

Any ideas to have the total of these calculus at a question level and be able to see this total data of the question when selecting a specificanswer?

Re: Count total

swuehl — Mon, 07 Dec 2015 17:06:42 GMT

Right, the corresponding count would probably be

=Count(DISTINCT Survey &'-'& Answer)

It's quite hard to see why your expression is not working like expected without knowing the full context.

Would it be possible that you share a small sample QVW (could contain some mock up data) that demonstrates your issue?

Re: Count total

Mon, 07 Dec 2015 18:34:42 GMT

I attached a document in the first message.

In the first column (Answers #) you can see the number of answers aggregated by Survey.

In the second column (Total Answers #) the total appears. However, when I select one answer it does not just display the selected answer but alls.

In case of selecting one or two answers, just these selected answers should appear, but the total count should be of all the possible answers.

Thank you.

Re: Count total

swuehl — Mon, 07 Dec 2015 18:52:18 GMT

Ok, that's something different than described in your OP.

It's understandable that you see all dimension values, since you cleared the selection in Answer field in the set expression.

Try multiplying your existing expression with an expression that return 1 for the selected values and 0 for the excluded:

=sum( total <Question, Survey> {<Answer>} aggr(count( {<Answer>}Answer),Survey, Answer))* COUNT(DISTINCT 1)

Re: Count total

Mon, 07 Dec 2015 18:59:42 GMT

Thank you.

It totally works.