Get Maximum of Sum value (set analysis)

[JonathanG]

Hi Guys,

There are such data:

Date (DD/MM/YYYY)	Place	Region	Value
01/08/2020	1	A	2
01/08/2020	1	B	7
01/08/2020	2	C	4
01/08/2020	2	D	8
01/09/2020	1	A	1
01/09/2020	1	A	9
01/09/2020	1	B	5
01/09/2020	2	C	7
01/09/2020	2	C	3

and I want to present  the Max Sum of Value of the newest date from field "Place" and ignore field "Region" on KPI chart, so the result will be = 15 (1+9+5) (bold).

Can i get that?

I have tried max({1<Place=>}aggr(sum({$<Date ={'$(=Max(Date))'}, Place>} Value), Value)) but it gives me 9.

Thank you

[tm_burgers · Accepted solution]

You are aggregating by Value, but you need to aggregate on Date to sum all the values. Otherwise it will give you the max value (which is 9).

Try

max({1<Place=>}aggr(sum({$<Date ={'$(=Max(Date))'}, Place>} Value), Date)) 

[Kushal_Chawda]

Are you looking to get data for specific Place,  If you take a max of Date, that date is available for other Place as well. What is the final  output you need?

[JonathanG]

I just realize that aggr concept, so i modified "Date" into "Place"

so i think it counted as solution 🙂

[JonathanG]

I am facing another problem 😞

I transform that data into pivot table, so Region "D" has null value.

when i click one by one all of the Region (except "D"), the KPI will stay 15

but if  i click Region "D", it will return 12 (4+8) (Max of Sum of last period)

How can this happen?