
1. Re: Calculation on Higher Level Data Attribute (nonblending)
Matt Lutton May 6, 2014 8:44 AM (in response to David Glusic)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 (nonblending)
Matt Lutton May 6, 2014 8:52 AM (in response to Matt Lutton)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 (nonblending)
David Glusic May 6, 2014 9:16 AM (in response to Matt Lutton)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 (nonblending)
Matt Lutton May 6, 2014 9:21 AM (in response to David Glusic)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 (nonblending)
David Glusic May 6, 2014 9:33 AM (in response to Matt Lutton)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.
Thanks for the quick reply.

SampleWB.xlsx 17.6 KB

SampleWB.xlsx 17.6 KB

SampleEntryDays EX 2.twbx 36.7 KB


6. Re: Calculation on Higher Level Data Attribute (nonblending)
Matt Lutton May 6, 2014 9:39 AM (in response to David Glusic)Here you gojust 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 IDlevel data. Hopefully that makes some sense.
See the attachedare 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 rearrange 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 (nonblending)
Matt Lutton May 6, 2014 9:45 AM (in response to Matt Lutton)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 startthis definitely makes "helping" much easier and more of a collaborative process. I really enjoy working through a thread like this onekeep it up!

8. Re: Calculation on Higher Level Data Attribute (nonblending)
David Glusic May 6, 2014 9:46 AM (in response to Matt Lutton)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 (nonblending)
Matt Lutton May 6, 2014 9:56 AM (in response to David Glusic)No worriesas can be seen by this thread, I still have to work through kinks and misinterpretations as well, and I sometimes struggle with understanding a stated goalmock ups of results really help me work out a solution, but that's kindof a backward way to go about it... wish I could do the opposite, and understand the goal clearly from the start, but I suppose progress has been made... In terms of adding the WINDOW_COUNT to your calculation, this simply ensures that the second part of your calculation includes all IDs, rather than a COUNTD at a lower level, which is equal to 1 (as could be seen by your COUNTD ID column you had in the view). If we set up a WINDOW_COUNT calculation (with the same compute using settingsaddressing on Month, then ID, restarting every Month; Note that if we had IDs on the Rows shelf and Month on the Level of Detail, the addressing would be the opposite) and add it to the crosstab, we can see the results:
Using crosstabs while setting up Table Calcs is hugely valuable, and is something I did not realize for some timeuntil 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 futureI'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!