Underlying values from Max(Aggr(Sum()))

[Osmatash]

I have a table with columns AREA, YEAR, MONTH, HOUR, VALUE_TYPE & VALUE. In a bar chart, I would like to show the maximum value for the sum of all value types, per year, month and hour. The dimension I'm using is YEAR. 

I can achieve this by using Max(Aggr(Sum(VALUE), YEAR, MONTH, HOUR)).

Now to the issue I'm experiencing: I would also like to add to the same chart, bars for the underlying values for different types in VALUE_TYPE, that make out that maximum. 

For example, VALUE_TYPE can be "A", "B" or "C". For each year I want to display the maximum value of A+B+C during that year. And I also want to display what the individual values of A, B and C were at that point. Is this possible using calcualted fields? It needs to be dynamic, so I can choose different types of AREA and get new max value and underlying values for different combinations of areas. 

I've spent 10+ hrs trying to figure this out, very grateful for assistance!

[kaushiknsolanki]

Hi,

Can you share some sample data along with the expected output? This will help us understand your needs.

[Osmatash]

I've attached an excel file with example data and it also includes an image of how I want the combo chart to look. EDIT: Nevermind, for some reason it takes ages to load it even though it's 16 kb.

I will include screenshots of the data and the image in the post.

So for any area or combination of areas, for every year I want to plot the highest sum value of all VALUE_TYPE (highlighted red in the graph). Then I want to add to the plot what the individual values of those VALUE_TYPEs were at that hour. 

[Osmatash]

Please let me know if the problem is formulated in a comprehensive way.

[tresesco]

If I would like to work on this, sample data in excel would help me to import in my app. Give another try to attach now. Also try to share the expected values on a,b,c