Rangesum showing only at year end

[mikecherry]

Hi all,

I am trying to use rangesum and I want to sum just what's happened during that year for the column called "Preferred Returns".

I want also to show that amount in another column (Called "Preferred Returns" too) against the 31/12 of each year.

This is the formula I am using for the circled section (See the picture below):

above(Rangesum(above(sum(Calls+[Gross Investment Distributions]),0,rowno())),1)*$(vPrefReturnRequired)*((Date#(Date)-above(Date#(Date)))/366)

Attached you can find the app. 

Please let me know if you can help me with this.

Thanks

[mikecherry]

If you have time to help me with this would be really appreciated 😄 !!!

[sunny_talwar]

I will see what I can do. This is fairly complicated and it might take some time to build this out completely.

[mikecherry]

OK no pressure :D. I knew it was almost impossible to achieve through the front end 😞

[sunny_talwar]

One issue that I see is that the return is a user input... if we do it in the script it will have to hard-coded in the script... or anytime a user wants to change its value, the dashboard will have to be reloaded. Would that work for you?

[mikecherry]

Yes it is fine. 

[sunny_talwar]

Try this

[Table]:
LOAD Year,
     Date,
     If(Mod(Year(Date), 4) = 0, 366, 365) as NoOfDaysYear,
     If(Date = Floor(YearEnd(Date)), 1, 0) as YearEndFlag,
	 [Calls],
	 [Gross Investment Distributions],
     RangeSum([Calls], [Gross Investment Distributions]) as [Cash Flows]
 FROM [lib://Desktop/Test Carry.xlsx]
(ooxml, embedded labels, table is Sheet1);

FinalTable:
LOAD *,
	 If(YearEndFlag = 1, RangeSum(Peek('**bleep** Prev Cumulative Cash Flows'), [Prev Cumulative Cash Flows]), Peek('New **bleep** Prev Cumulative Cash Flows')) as [New **bleep** Prev Cumulative Cash Flows],
	 RangeSum(Peek('**bleep** Prev Cumulative Cash Flows'), [Prev Cumulative Cash Flows]) as [**bleep** Prev Cumulative Cash Flows],
	 RangeSum([Cumulative Cash Flows], If(YearEndFlag = 1, RangeSum(Peek('**bleep** Prev Cumulative Cash Flows'), [Prev Cumulative Cash Flows]), Peek('New **bleep** Prev Cumulative Cash Flows'))) as [New Cumulative Cash Flows];
LOAD *,
	 Date - Peek('Date') as Days,
	 RangeSum(Peek('Cumulative Cash Flows'), [Cash Flows]) as [Cumulative Cash Flows],
     (Peek('New Cumulative Cash Flows') * $(vPrefReturnRequired) * (Date - Peek('Date')))/NoOfDaysYear as [Prev Cumulative Cash Flows]
Resident Table
Order By Date;

DROP Table Table;

Users can change the rate on the front end and trigger a reload to see changes to preferred returns