Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count total

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.

  

QuestionAnswerCount (Distinct Answer) aggregated per SurveyCount total
1174698
1273698
13408698
1490698
1553698

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

QuestionAnswerCount (Distinct Answer) aggregated per SurveyCount total
117474

Thank you.

Best regards.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

6 Replies
swuehl
MVP
MVP

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))

Not applicable
Author

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?

swuehl
MVP
MVP

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?

Not applicable
Author

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.

swuehl
MVP
MVP

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)

Not applicable
Author

Thank you.

It totally works.