Range Sum and Multiple Dimensions

[mikecherry]

Hi there,

I am trying to recreate a Rangesum in a line chart but I am encountering some issues.

I am trying to replicate the "Dist" column from the left hand side table into the line chart so I can see "Dist" by Fund Vintage.

The formula I have on the left is a simple Rangesum(Above($(vRDTransDist),0,rowno())) whilst the formula I am using in the line chart is :

 aggr(Rangesum(above($(vRDTransDist),0,Rowno())),Date_QuarterEnd,Fund_Vintage)

The variable in both is :

vRDTransDist =  SUM({<Data_Type={"Trans"}, Data_Measure={"Dist"}, Date_ID <=$(vReportDate)"}>}$(vMeasure))/vDivisor

It looks like is not doing the rangesum but it's just giving me back the quarterly transactions.

Hope someone can help.

Thanks

Michele

[OmarBenSalem]

when using rangesum with multiple dimensions; always agg with the last dimension and revert back..

in ur case:

 aggr(Rangesum(above($(vRDTransDist),0,Rowno())),Fund_Vintage,Date_QuarterEnd)

[mikecherry]

Thanks for your replay.

It doesn't work. It's not accumulating the numbers.

[OmarBenSalem]

what are the dimensions u're using in ur line chart?

Dim1 and dim2?

if one of the dimensions is calculated; year(date); please create it in the script then use it in the aggr with this logic:

aggr(

rangesum(above(sum(YourMeasure),0,rowno()))

,Dim2,Dim1)

This WILL work; I'm dead sure.

[mikecherry]

I am using Date_Quarterend which is  a quarterly date and I am using a normal dimension which is showing the different lines.

[mikecherry]

Is it maybe not working because the line chart is randomly sorting data so, the Rangesum does not work properly ?

[OmarBenSalem]

Is it possible to share ur app with me?

[mikecherry]

Quite tough to share as it is quite big and with some confidential data.

You think anyway that just using AGGR function should solve the problem ?

If it's not fixing the problem usually what are the causes that might cause the AGGR formula not to work ?

Thanks

[sunny_talwar · Accepted solution]

Not sure if it will help, but try this

Aggr(RangeSum(Above($(vRDTransDist), 0, RowNo())), Fund_Vintage, (Date_QuarterEnd, (NUMERIC)))

[mikecherry]

Sunny the Hero thanks ! It looks like is working