topic Need Sum of values for most recent date by dimension in App Development

Need Sum of values for most recent date by dimension

datagrrl — Mon, 21 Nov 2022 15:44:29 GMT

I did find two similar posts, but they didn't address this exact question.

This seemed pretty simple at first, to just get the sum for the max date, but the problem is I need the sum for the max date for each value of the dimension. So while 11/23/22 is the max date, I will not have values for the other countries.

I thought something like this might work, but I think I am missing something obvious.

=Sum(Aggr(If(Date = Max(TOTAL <Country> Date), Sales),Date, Country))

Data:

Expected Results

TIA, Sample App Attached

Re: Need Sum of values for most recent date by dimension

Aditya_Chitale — Mon, 21 Nov 2022 17:44:29 GMT

try this:

test:
LOAD
Country,
"Date",
Sales
FROM [lib://Test1121/TestTable.xlsx]
(ooxml, embedded labels, table is Data);

left join (test)

Load
Country,
max("Date") as max_date
resident test group by Country;

use below expression in frontend expression:

Sum({<date={"$(=max_Date)"}>}sales)

Regards,

Aditya

Re: Need Sum of values for most recent date by dimension

datagrrl — Mon, 21 Nov 2022 18:44:11 GMT

I ended up getting what I expected for a single country with this

SUM({<Country={'Mexico'},Date={"$(=Max({<Country={'Mexico'}>}Date))"}>}Sales)

Since I need a KPI for a specific dimension value, this worked for me.

Re: Need Sum of values for most recent date by dimension

vinieme12 — Tue, 22 Nov 2022 09:24:56 GMT

would rather avoid row level expression search entirely

left join (test)

Load
Country,
,max("Date") as Date

,1 as flag_maxDate
resident test group by Country;

use below expression in frontend expression:

Sum({<flag_maxDate={1}>}sales)