Sum of multiple values per max aggr date

[nsm1234567]

Hi there,

I'm having some trouble with an aggr expression I've been working on.  If you look at the attached, I'm trying to  get the sum of the points where the date is the most recent date in the range.  The below sort of works, but doesn't "roll up" nicely if the chart were to be aggregated  at a higher level than store.  I also obviously have the Null total.  Can someone perhaps assist me with a better way to write this expression?

=if( Date= max(TOTAL<Store> aggr(max(Date),Store)),sum(Points) )

[olivierrobin]

hello,

for the null total, you can suppress it by checking "No total" in total subsection of expressions

for the aggr, you have to specify all the dimensions used in the aggregation.

you can try to use pick(dimensionality(),total lvl 1, total lvl 2, ....) to have different totals depending on aggregation level

[sunny_talwar · Accepted solution]

Try this

=Sum(Aggr(If(Date = Max(TOTAL <Store> Date), Sum(Points)), Store, Date))

[nsm1234567]

Hey Sunny,

Thanks a lot for this.  I ended up aggregating the points value in the load script and then using firstsortedvalue to fetch the first entry based on the sort order of the date, but yours does exactly what I asked.  Just couldn't get the syntax right. 

Thanks again