topic Re: Rangesum showing only at year end in App Development

Rangesum showing only at year end

mikecherry — Sat, 16 Nov 2024 19:45:43 GMT

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

Re: Rangesum showing only at year end

sunny_talwar — Mon, 04 Nov 2019 16:51:41 GMT

May be this

if(left(Date,5)='31/12', above(Rangesum(above(sum(Calls+[Gross Investment Distributions]),0,rowno())),1)*$(vPrefReturnRequired)*((Date#(Date)-above(Date#(Date)))/366), 0)

Re: Rangesum showing only at year end

mikecherry — Mon, 04 Nov 2019 16:54:30 GMT

Nope I want to show the Rangesum of that expression against that cell in this way it's only returning the value that is against that cell

Re: Rangesum showing only at year end

sunny_talwar — Mon, 04 Nov 2019 16:56:22 GMT

I am not sure I follow... what would you want to see here if not -29,270?

Re: Rangesum showing only at year end

mikecherry — Mon, 04 Nov 2019 17:02:18 GMT

That should be the sum of all the preferred returns for the year (-98,511).

The second one against 31/12/2013 should be around -441,949 ish

Re: Rangesum showing only at year end

sunny_talwar — Mon, 04 Nov 2019 17:11:23 GMT

May be this

Aggr(If(Left(Date, 5) = '31/12', RangeSum(Above( Above(TOTAL Rangesum(Above(TOTAL Sum(Calls+[Gross Investment Distributions]), 0, RowNo(TOTAL))), 1) * $(vPrefReturnRequired)*((Date#(Date)-Above(TOTAL Date#(Date)))/366), 0, RowNo(TOTAL))), 0), Year, Date)

Re: Rangesum showing only at year end

mikecherry — Mon, 04 Nov 2019 17:13:56 GMT

Sunny Talwar my hero as always !!!!

Thank you so much !

Re: Rangesum showing only at year end

mikecherry — Mon, 04 Nov 2019 18:08:14 GMT

Hi Sunny,

It's me again. I am actually trying to achieve the same table here in the excel spreadsheet attached.

Do you think you can help ?

Re: Rangesum showing only at year end

sunny_talwar — Mon, 04 Nov 2019 20:17:53 GMT

Does this has to be done on the front end? Can we do this in the script?

Re: Rangesum showing only at year end

mikecherry — Tue, 05 Nov 2019 09:13:17 GMT

Yes we can !

Re: Rangesum showing only at year end

mikecherry — Tue, 05 Nov 2019 11:00:40 GMT

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

Re: Rangesum showing only at year end

sunny_talwar — Tue, 05 Nov 2019 11:24:03 GMT

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

Re: Rangesum showing only at year end

mikecherry — Tue, 05 Nov 2019 11:26:25 GMT

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

Re: Rangesum showing only at year end

sunny_talwar — Tue, 05 Nov 2019 11:42:40 GMT

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?

Re: Rangesum showing only at year end

mikecherry — Tue, 05 Nov 2019 12:07:00 GMT

Yes it is fine.

Re: Rangesum showing only at year end

sunny_talwar — Tue, 05 Nov 2019 13:59:10 GMT

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