Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
Try this.
Sum({$<COMPANYDAY = {'=$(=Max({<COMPANY,COMPANYMONTH,COMPANYYEAR>} COMPANYDAY))'}>} CODECOUNT)
@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
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)
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