Extracting the max value

[Eldinkarisik]

Hi all,

I am trying to extract the max value of a period in months. So in the following table we see a YearMonth Column with what the max was in that month for each month with the following expression: 

=sum(aggr(max(QTY),YearMonth,DEPOT_NAME, RTI_NAME, RTI_State))

 However, it provides me a sum value instead of providing me which month was the max with its value. In this case I do not want to know the max of each month but when I select for example year 2019 that I get a max value (not interested which month) . In addition, when I would select a specific year and month the for example 2019-01 the value should change accordingly. 

Another, complexity is that the max per month is based on the different kind of items and its status on different dates. For example: 

Date	Item	Status	QTY
01-01-2019	A	Broken	50
01-01-2019	A	Non_Broken	100
01-01-2019	B	Broken	0
01-01-2019	B	Non_Broken	50
02-01-2019	A	Broken	60
02-01-2019	A	Non_Broken	50
02-01-2019	B	Broken	40
02-01-2019	B	Non_Broken	100

So, the max of this will be in QTY: 01-01-2019 has together 200 and 02-01-2019 = 250 and when selecting for example 01-2019 it gives the 250. 

[jensmunnichs]

Right now, your aggr() function is creating an invisible table with Max(QTY) grouped by those 4 dimensions. This means that you don't have 1 max value per YearMonth, instead you have way more (1 per YearMonth, DEPOT_NAME, RTI_NAME, RTI_State combination). You then use Sum while only using 1 dimension, YearMonth. This means that all those max values with the same YearMonth will be added together, hence the sum value.

Instead, if I'm understanding your requirement correctly, you want to sum the values per Date (regardless of Type/Item), and then find the Max value from that list, grouped by YearMonth (or just year). If that's correct, I think it would be best to create a pivot table with Year and YearMonth as dimensions and the following expression:

=Max(Aggr(Sum(QTY), Date))

Let me know if that works for you.

[Eldinkarisik]

I have tried that expression and unfortunately it gives me wrong numbers for each month. It does give me the max within that period but then the value of that month is incorrect. 

So, in the post in the first table where YearMonth and the expression is given, the month 2019-07 is not anymore 4255 but now 131905. This also applies to other months as well. However, it does provide that in 2019-07 the max is identified but with a wrong value. 

[jensmunnichs]

Would you be able to post your application or at least (part of) your data so I can test a few things?

[Eldinkarisik]

Here is a sample of the data for 2019

[Eldinkarisik]

This should be the solution: 

[jensmunnichs · Accepted solution]

I'm not sure how you get 5722 as your max, using the data set you gave me I believe the max is 4721, which happened in April. Have a look at the attached file (I've simplified the field names a bit in script).

I even checked what should be the max value in a pivot table in Excel:

[Eldinkarisik]

It worked, only there were some problem with the source data on how QTY is extracted that made the results a bit confusing. However, when I fixed that your set analysis worked. 

Thank you!

[nilesh_gawali]

=Max(Aggr(Sum(QTY), Date))

It's the same issue for me, but the scenario is a little different.

As soon as we select a date range, I want the sum of values (from another column) of the maximum date for that range.

e.g. If I select the range 1-Jan to 4-Feb and the maximum date present is 28-Jan, then it should take a sum of values (present in another column) for 28-Jan.