topic Re: How to: Sum a value per max day in a given month in App Development

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

AnalyticsDev — Wed, 17 May 2023 14:12:59 GMT

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?

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

Chanty4u — Wed, 17 May 2023 16:46:10 GMT

Try this.

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

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

Kushal_Chawda — Wed, 17 May 2023 22:22:26 GMT

@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

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

BrunPierre — Thu, 18 May 2023 06:36:41 GMT

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)

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

AnalyticsDev — Wed, 24 May 2023 13:34:03 GMT

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