Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikecherry
Contributor III
Contributor III

Rangesum showing only at year end

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)

Capture Rangesum.PNG

Attached you can find the app. 

 

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

 

Thanks

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

15 Replies
sunny_talwar

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)
mikecherry
Contributor III
Contributor III
Author

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

sunny_talwar

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

image.png

mikecherry
Contributor III
Contributor III
Author

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

 

sunny_talwar

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)
mikecherry
Contributor III
Contributor III
Author

Sunny Talwar my hero as always !!!!

 

Thank you so much !

mikecherry
Contributor III
Contributor III
Author

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 ?

sunny_talwar

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

mikecherry
Contributor III
Contributor III
Author

Yes we can !