
1. Re: Calculation to return the maximum value for each user?
Joe Mako Dec 15, 2012 12:47 PM (in response to Mike Greening)There are quite a few routes to get the result you are asking for here, an aggregation of an aggregation. This can be done with stacked bar marks and a reference line, or a WINDOW_SUM() table calc.
Attached are examples two. If your situation is more complex or you have other requirements or constraints, another route may be a better fit.
Notice on the table calc worksheet that the pill MAX(Value) is set to be ignored in table calculations, this causes it to be used for partitioning, and the compute using (addressing) of the table calc pill is set to User. To get the label at the end of the bar, stacked marks was turned off.
The table calc route would enable derivative calculations or other display options not available with the simple stacked bar marks.

count agg.twbx.zip 4.0 KB


2. Re: Calculation to return the maximum value for each user?
Mike Greening Dec 19, 2012 9:00 AM (in response to Joe Mako)Thanks Joe, this appears to have worked! I'm not sure why yet, but I'll figure it out Thanks for your help!

3. Re: Calculation to return the maximum value for each user?
Paul Hare May 15, 2013 7:43 AM (in response to Joe Mako)Joe:
I have a variation on the above problem:
Requirement User Score
1 A 1
1 B 2
1 C 0
2 A 2
2 B 3
2 C 3
3 A 2
3 B 0
3 C 0
Want to be able to display the Maximum score by requirement and the related user(s), such that:
1 B 2
2 B 3
2 C 3
3 A 2
Make sense?

4. Re: Calculation to return the maximum value for each user?
Joe Mako May 24, 2013 8:26 AM (in response to Paul Hare)how about something like the attached?

Highest Score.twbx.zip 12.7 KB


5. Re: Calculation to return the maximum value for each user?
Leonardo Faoro Nov 1, 2014 10:26 AM (in response to Joe Mako)Hi Joe
I just found this topic today, and it helped me resolve an issue that I had been dealing with for a long time. Unfortunately I don't understand how the calculation below works. Would you please be able to explain? Or point to a place where I could learn these functions?
IF FIRST()==0 THEN
WINDOW_SUM(MAX(1),0,IIF(FIRST()==0,LAST(),0))
END
many thanks
Leo 
6. Re: Calculation to return the maximum value for each user?
Aaron Clancy Nov 1, 2014 11:27 AM (in response to Leonardo Faoro)Hi Leonardo,
That calculation has some redundant logic in it which could probably cause it to be a little confusing.
First off, It is the equivalent of if index() = 1 then size() end
The calculation will only be evaluated for the first position so any reference to a comparator of first()==0 will always evelate to true in any subsequent logic.
The parameters taken in for window_sum are:
window_sum(measure, starting position relative to location, ending position relative to location)
If the position is a number then it's the starting position relative to the location in the partition.
If it is first() or last() then it is the first or last position in the partition.
In this example the starting position is the first one or "first() = 0" so your starting point to sum is the first value in the partition.
The ending position is a calculation:
IIF(FIRST()==0,LAST(),0)
This is an if then statement. If true then the ending position is last() else ending position is 0 positions away from current location.
first() in this instance can never evaluate to anything other than 0 because you never leave the first location in the partition.
so the ending point is always the last record in the partition.
This amounts to "How many values do I have in my partition?" This can be achieve with the size() function.
Simpler calc:
if index() = 1 then size() end
Logic: If I am the first record in the partition output the number of values in the partition.
Let me know if that helps your understanding of table calcs.

7. Re: Calculation to return the maximum value for each user?
Leonardo Faoro Nov 7, 2014 10:48 AM (in response to Aaron Clancy)Hi Aaron, thank you very much for the thorough explanation. I think I get it. In my use case, what I want is not necessarily the first item in the partition, I want the item with the maximum value for that field. So does sorting by this field influence what this calculation gets?
thanks
Leo 
8. Re: Calculation to return the maximum value for each user?
Aaron Clancy Nov 7, 2014 11:46 AM (in response to Leonardo Faoro)Hi Leonard,
I was just explaining the calc in question, it might not relate at all to your scenario.
If you want the max mark to show and restrict the rest there are a few ways to do this.
If your data is hierarchical meaning that your lower level value (color) exist in only one instance of the higher level then sorting is fairly straight forward and you can sort your color dimension.
If it's not then you'll need to filter to the max value of the lower level with respect to the higher level.
Here's an example I created that shows the difference, outlining the two scenarios and the common mistake that people make when they try to sort a nonheirarchical relationship.

9. Re: Calculation to return the maximum value for each user?
Leonardo Faoro Nov 7, 2014 7:15 PM (in response to Aaron Clancy)thank you Aaron. Clearly I have a lot to learn on this topic still
Leo

10. Re: Calculation to return the maximum value for each user?
Manish Baid May 12, 2015 10:38 PM (in response to Mike Greening)1 of 1 people found this helpfulHi,
I have another case where i need max for a particular level 
Say for example 
A 1
A 2
A 3
B 1
B 5
C 2
C 7
Desired output 
A 1 3
A 2 3
A 3 3
B 1 5
B 5 5
C 2 7
C 7 7
Here 3 is for the values of set A, 5 is the max for the values of set B and so on.
Thanks in advance.

11. Re: Calculation to return the maximum value for each user?
Alexander Mou May 12, 2015 11:09 PM (in response to Manish Baid)Please start a new thread.

12. Re: Calculation to return the maximum value for each user?
David Hines Oct 3, 2015 6:54 AM (in response to Manish Baid)1 of 1 people found this helpful{FIXED [Letter Column] : MAX([Number Column]}

13. Re: Calculation to return the maximum value for each user?
ed.d.1 May 11, 2017 6:04 AM (in response to Aaron Clancy)I just wonder if you have ever discussed it here. I am just looking for a function to create a calculated field which could exclude two highest and two lowest values in a given set. thank you in advanced!

14. Re: Calculation to return the maximum value for each user?
Matthew Fahy Dec 10, 2017 6:08 AM (in response to Mike Greening)Use the FIXED Function:
{FIXED [Criteria 1],[Criteria 2],... : [Calculation]}
in your example the solution would be:
{FIXED [User] : MAX([Value])}
in addition if you would like it to be a table calculation on the value, you would need to use the FIXED function as well, but without any criteria (columns) listed:
{FIXED : MAX([Value])}