How to: Sum a value per max day in a given month

[AnalyticsDev]

I feel like this should be an easy solve, but Im having difficulty.

I have colums such as:

COMPANY,COMPANYDAY,COMPANYMONTH,COMPANYYEAR,CODECOUNT

CompanyA, 1/1/2023, Jan, 2023, 1

CompanyA, 1/15/2023, Jan, 2023, 3

CompanyA, 1/15/2023, Jan, 2023, 5

CompanyA, 2/1/2023, Feb, 2023, 2

CompanyA, 2/28/2023, Feb, 2023, 3

I have data that looks like the above for two years worth.  I want to take the maximum date per company, per year and month, and sum the values.  

For the example above for January 2023 Company A, the sum Im looking for is 8.  We find the max date in the record set for Company A, within January of 2023 and that max date is 1/15.  We sum the values 3 and 5.  

Again, for CompanyA, the sum Im looking for is 3.  We found the maximum date for the data given, in this case it is the actual last day of the month 2/28, sum the values which is only 3.

How do I approach this?

[Chanty4u]

Try this.

Sum({$<COMPANYDAY = {'=$(=Max({<COMPANY,COMPANYMONTH,COMPANYYEAR>} COMPANYDAY))'}>} CODECOUNT)

[Kushal_Chawda]

@AnalyticsDev  Add dimensions  COMPANY,COMPANYDAY and below expression

=if( COMPANYDAY= aggr(nodistinct FirstSortedValue(distinct COMPANYDAY,-COMPANYDAY),COMPANY,COMPANYMONTH,COMPANYYEAR), sum(CODECOUNT),0)

Uncheck "Include zero values" option

[BrunPierre · Accepted solution]

Hi, you could flag the max records within the script.

Data:
LOAD * Inline[
COMPANY,COMPANYDAY,COMPANYMONTH,COMPANYYEAR,CODECOUNT
CompanyA, 1/1/2023, Jan, 2023, 1
CompanyA, 1/15/2023, Jan, 2023, 3
CompanyA, 1/15/2023, Jan, 2023, 5
CompanyA, 2/1/2023, Feb, 2023, 2
CompanyA, 2/28/2023, Feb, 2023, 3
];

Left Join(Data)
LOAD COMPANY,
Date(Max(COMPANYDAY)) as COMPANYDAY,
'1' as MaxFlag

Resident Data
Group By COMPANY, COMPANYMONTH;

And then use this as the expression.

Sum({<MaxFlag={1}>}CODECOUNT)

[AnalyticsDev]

My data model might be a bit weird because of previous requirements... Considering that this is pulling from an excel sheet that I'll need to open each month to add in new data, I made a flag field in the excel to simplify and move on.    This is solved (for now).  Thanks