Aggregation across column before aggregation by Axis.X in Custom Expression

[Austin Fox]

I have a table of value,Date,and Area.

I currently have: ValueForMin([Date],[value]) plotted by BinByDateTime([Date],"Year",0)

This is ok except that when I have multiple Areas in a YearI only get the value of the oldest record regardless of area.

It would be better to get the sum of the oldest records by area.

The simplist of what I tried is:

Sum(ValueForMin([Date],[value]) over ([Area]))

Spotfire only tells me that is expression is invalid.

What is wrong with this expression or how can this be done

 

Thanks for your help!!

Austin

[Khushboo Rabadia]

Use Intersect after over i.e. Over(Intersect([Area]))

[Austin Fox]

This does give values but not the correct ones. I am not completely sure why but I suspect The ValueForMin function in Sum(ValueForMin([Date],[value]) over (intersect([Area]))) ends up acting on dates rather than the aggregated year, thus giving a huge value. Adding Intersect([Area],[Axis.X]) to make it explicit makes no difference. 

 

Replacing [Date] with [Axis.X] in ValueForMin would seem like the right thing to do but it says invalid type.

 

Removing the Sum seems to give the ValueForMin for that year.

 

And doing the sum as part of the aggregation makes the value huge --> not sure why though.

 

Thoughts  Thank you!