1 2 Previous Next 18 Replies Latest reply on Nov 23, 2013 6:41 AM by Toby Jutras

# Grouping Dimensions by Aggregate Sums

Hello,

I have medical data split out by individual over two years and want to be able to group individuals uniquely by year based on the sum of the expenses that the individual has accumulated for the given year (ex: \$0-\$29,999, \$30,000-\$49,999, etc). The group cannot be based on the sum over both years. I'm aware that I can just manually group the individuals myself, but if I upload new data, I'd like for it to populate/group correctly based on new data.

Data Example:

IndividualReporting PeriodPaid \$
A2012\$20,000
B2012\$30,000
C2012\$15,000
B2012\$50,000
C2012\$100,000
A2011\$120,000
B2011\$20,000
B2011\$70,000

How I would like it to look:

Paid Band Individuals Paid \$ Individuals Paid \$ Reporting Period 2012 2012 2011 2011 \$0 - \$49,999 1 \$20,000 0 0 \$50,000 - \$99,999 1 \$80,000 1 \$90,000 \$100,000 - \$199,999 1 \$115,000 1 \$120,000

Thanks!

• ###### 1. Re: Grouping Dimensions by Aggregate Sums

Create a calculated filed based on Paid\$. You can write case statement to break the Paid\$ into Paid Band. This field will be your dimensions. If you use the reporting period as your column, it will garantee that the aggregation will calculated by year (i.e., it won't sum over two years or more).

that should take care of what you are looking for.

• ###### 2. Re: Grouping Dimensions by Aggregate Sums

If you can post a sample packaged workbook (.twbx file) that demonstrates how your data looks, someone can help you create an appropriate solution.  Its far easier to help you once we can interact with a workbook.  Thanks!

• ###### 3. Re: Grouping Dimensions by Aggregate Sums

Here is an example of what the basic data would look like.

Thanks!

• ###### 4. Re: Grouping Dimensions by Aggregate Sums

Hi Gengming,

Verify this workbook, based on your data example, to see if it solves your problem.

Best,

Ramon

• ###### 5. Re: Grouping Dimensions by Aggregate Sums

Hi Ryan,

You can achieve this by creating two calculated fields.

1. Paid band (aggregate sum group)

2. No of individuals (distinct count of Individuals)

Note: don't forget to place the dimension "Individual" to detail shelf.

Please find the attached workbook with the below result from the sample data. As Matthew Lutton mentioned please attach a packaged workbook next time, so your questions will be answered quickly.

• ###### 6. Re: Grouping Dimensions by Aggregate Sums

Lol, I am 3 mins late

• ###### 7. Re: Re: Grouping Dimensions by Aggregate Sums

Yes, but your solution was actually more accurate because it used the sum(Paid) to create the categories and used a distinct count.  The important piece is to include "Individual" on the Level of Detail shelf when using calculations like this, as pointed out previously.  Ramon's solution can be edited to reflect this (see attached)

Also, I believe the count should be a COUNTD (distinct count) in Ramon's example, which requires the data be extracted by Tableau first (count distinct is unavailable for Excel data sources--a data extract resolves this)

• ###### 8. Re: Grouping Dimensions by Aggregate Sums

Hi Indumon,

I think your solution is the correct one. I'm going to check it again.

Best,

Ramon

• ###### 9. Re: Grouping Dimensions by Aggregate Sums

Feels excited while working together for one solution Matt Lutton Ramon Martinez

• ###### 10. Re: Grouping Dimensions by Aggregate Sums

Hi Matthew,

Your are totally right. As I mentioned above, Indumon's solution is the right one.

Best,

Ramon

• ###### 11. Re: Grouping Dimensions by Aggregate Sums

Thanks everyone for the help thus far.

Unfortunately, my data has multiple paid lines that correspond to each individual so I have to SUM(Paid) to figure out the totals for each individual for the given reporting periods. This makes the creation of a paid band difficult since you cannot use an aggregation in an IF statement. Please refer to the data workbook example I posted above to see my problem.

Thank you so much again!

• ###### 12. Re: Re: Grouping Dimensions by Aggregate Sums

Please check my workbook attached. It was using Sum() in IF condition.

• ###### 13. Re: Grouping Dimensions by Aggregate Sums

Awesome thank you!

• ###### 14. Re: Grouping Dimensions by Aggregate Sums

How would I get it so that I could view the subtotals of these groupings for analysis (ex: % difference between the \$50,000 - \$99,999 grouping)?

1 2 Previous Next