9 Replies Latest reply on May 6, 2014 9:56 AM by Matt Lutton

# Calculation on Higher Level Data Attribute (non-blending)

Good morning, after much time spent on investigating this issue for internal project I'm currently stumped on how to solve.

I need to regularly calculate based on aggregations higher than my data set. My data set displays ID's and Products, with Entry Date as an attribute of ID, not product. When I create a field to calculate days from entry, my result is incorrect due to duplicated data. Similar to issue when blending two data sources and taking an average, this unfortunately does not work as my count of records per ID varies.

What I'd like to do is create a simple sum of days from entry, and an average of days per ID. Which can be displayed via chart or table (as shown on sample sheets).

I've tried many combinations, including Window_sum(Average), Window_Avg(Sum), to no avail.

I've attached a  sample workbook for reference of how the output may look like (we never will have ID displayed due to overall number of records).

Thanks!

• ###### 1. Re: Calculation on Higher Level Data Attribute (non-blending)

What is your expected end result/output in a table view?  Is it what I see in Sample 3?

• ###### 2. Re: Calculation on Higher Level Data Attribute (non-blending)

Is this the Output you'd like to see?  If not, can you let me know what the expected output is?

Thanks!

• ###### 3. Re: Calculation on Higher Level Data Attribute (non-blending)

Matt,

Thanks for the quick reply and sample workbook. The expected values in this case are 791, and 1061 for sum of days, and 35.95, and 53.05 for average per ID.

It seems the calculation you provided is getting closer as you've been able to attach ID to the details shelf without the annoying duplication. The calculation would need to take the average days per ID, then sum each average to get the correct total. For the average per ID calculation it would be the number of days / COUNTD([ID]).

Thanks again!

• ###### 4. Re: Calculation on Higher Level Data Attribute (non-blending)

I'm not sure what you mean exactly -- would it be possible for you to provide a mockup of your expected output in a Crosstab format?  Also, the math used to compute your expected results would help as well, as I am not following where the  "expected values" you listed are coming from.

• ###### 5. Re: Calculation on Higher Level Data Attribute (non-blending)

Matt,

I'm attaching the base Excel file, which contains the logic used to calculate the correct values. After reviewing your initial file further I was able to change the Window_Sum(Sum) into Window_Sum(Avg) and was able to get the correct sum of days as 791 and 1061.

The only piece missing is the average per ID as COUNTD doesn't work if it's calculating at the ID level of detail.

• ###### 6. Re: Calculation on Higher Level Data Attribute (non-blending)

Here you go--just needed a WINDOW_COUNT on the second part of your calculation.  With ID on the Level of Detail (Detail shelf), we can use WINDOW calcs to ensure our calculations include the ID-level data.  Hopefully that makes some sense.

See the attached--are we done?

Note that if you wanted to create other views, you'd need ID on the Detail shelf.  I created a bar chart example in the last sheet, for reference.  With the compute using settings explicitly defined for each measure, we can re-arrange the view for different results without breaking the Table Calculation.

Hope this helps.  The best resource for learning/understanding this stuff is probably here:

Want to Learn Table Calculations? Here’s How! | Drawing with Numbers

Cheers!

• ###### 7. Re: Calculation on Higher Level Data Attribute (non-blending)

Also, thanks for working with me on providing more details about your expected output, and for providing a packaged workbook and a description of the problem from the start--this definitely makes "helping" much easier and more of a collaborative process.  I really enjoy working through a thread like this one--keep it up!

• ###### 8. Re: Calculation on Higher Level Data Attribute (non-blending)

Matt,

This works exactly as described, thank you! I've done a fair amount of development with table calcs but for some reason the Address / Partitioning logic isn't quite sticking yet (even after playing with calcs to get the desired result). Typically this is due to the number of quick filters and other measures required for the desired output.

Thanks again!

• ###### 9. Re: Calculation on Higher Level Data Attribute (non-blending)

Using crosstabs while setting up Table Calcs is hugely valuable, and is something I did not realize for some time--until I noticed almost every post by Jonathan Drummey had a "workout" view which showed a crosstab of how he came up with his Table Calc results.

Anyway, I'm sure this makes sense to you, but writing it out helps me gain a firmer understanding as well.

Feel free to ping/email me on any Table Calc problems in the future--I'd love to work through more examples together, if the opportunity presents itself.  I seem to learn more (or maybe its better?) when working collaboratively with others, as well.

Cheers!