Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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) )
Try this
=Sum(Aggr(If(Date = Max(TOTAL <Store> Date), Sum(Points)), Store, Date))
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
Try this
=Sum(Aggr(If(Date = Max(TOTAL <Store> Date), Sum(Points)), Store, Date))
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