Announcements
Discover what’s possible with embedded analytics! March 6, 10 AM ET SIGN UP!
cancel
Showing results for
Did you mean:
Not applicable

## At a road block. Think Aggr is the answer, but how?

Hi Everyone

Attached is a spreadsheet that shows my issue. I am working with survey data. There are 11 phases of survey, and not all questions are asked in each phase. I am trying to show the percentage responded to all questions in a single pivot chart.

The question "Eats Raw Food" was asked in all 11 survey phases, meaning the total population exposed to the question was 12.4bn. As only 5,1bn answered, the correct response percentage is 42%.

The question "Likes Fruit" was only asked in 7 survey phases. The correct response percentage is 32%.When the population for all phases are included, the response percentage drops to an incorrect 23%.

When I use TOTAL in the SUM, the population value for all phases is used (12.4bn) instead of only the population where the question was asked.

I think I need to use a SET to compare the possible survey phases to sum the population, without using TOTAL.

In short, I'm trying to place the equivalent of a Sum(Total xxx) in a cell, with a different Sum(Total xxx) per line, in the same column.

1 Solution

Accepted Solutions
MVP

Maybe like attached?

6 Replies
MVP

Sorry, I don't understand how you get from the 12.4bn population to the 8.99bn population when reducing the amount phases. Where does your population number come from?

Not applicable
Author

Hi. It doesn't really matter where the population data comes from, the survey company is providing it. However, the 8.9bn is the Sum(Total xxx) when the Survey field is constrained to only the phases where the second question was asked. So, 12.4bn is the total population for all survey phases, 8.9bn is the total population for phases 5 to 11 where the second question appeared.

MVP

Yes, it doesn't matter where it comes from i.e. what your data source is or how the actual numbers look like.

But your data model does matter. Could you describe your model a bit closer or post a small app with mock up data that is close to your data model?

I was confused by your repeated population numbers in your excel file, as far as I've understood, the population numbers are different for each line and only the sum across all surveys results in e.g. 12.4bn. Is this correct, meaning your excel file is not correct in that point?

First thing that came to my mind was using a TOTAL qualifier with a field list, but I am not sure if this is applicable in your case.

So, your data model and the visualization / chart you want to use does matter, please post more details.

Not applicable
Author

Hi. Attached is an example QVW of what I'm trying to do. Thanks.

MVP

Maybe like attached?

Not applicable
Author

Thanks for your help. I knew it would be simple, I just couldn't see it!

Community Browser