cancel
Showing results for 
Search instead for 
Did you mean: 

Question about writing a formula

Former Member
0 Kudos

Hello All,

I am not sure if this is the correct forum to ask this questions, but I'm a little stuck. There might be a simple solution, but it''s certainly skipping me.

Basically, for all our investments, we create valuation records with quarter end date (3/31, 6/30, etc.) with a new value, whether higher or lower in SalesForce. For example, the object contains fields valuation_date, investment_cost and investment_value. I've queried the data for all valuations with valuation_date>=6/30/2011 in a crosstab. I want to write a formula that will give me the change in the investment_value between the two valuation dates and this is where I'm lost.

Any insight is much appreciated. Thanks.

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Sumit,

Could you provide us a sample of how the cross-tab looks with some data too.

-Abhilash

Former Member
0 Kudos

Abhilash,

Thank you for you response. Here's a rough example of how the crosstab looks at the moment.

_______________ 6/30/2011_______9/30/2011

Investment1_____ $1000000_______$2000000

Investment2_____ $1500000_______$2500000

Investment3_____ $2000000_______$3500000

Basically there would be another column that would show the difference between the 9/30 values and 6/30 values. I hope this helps

Again the fields are: investment_date, investment_value and investment_name

I hope this helps.

Thanks,

Sumit

Edited by: SumitJoshi on Dec 21, 2011 5:11 AM

abhilash_kumar
Active Contributor
0 Kudos

Hi Sumit,

If you're using CR 2008, you can make use of the advanced cross-tab features available.

Follow the steps below to create a calculated member (a.k.a row or column) to show the difference:

1) In the Preview mode, right-click the column header that shows the last date. Considering 9/30/2011 as the last column, Right-click the column header > Calculated Member > Insert Column

2) A column with a blank header and zero values comes up

3) Right-click the blank column header and select Calculated Member > Edit Header Formula. Type this in the editor:

"Difference"

4) Now, the blank column header displays the text 'Difference'

5) Next, right-click one of the zero values in this column and select Calculated Member > Edit Calculation Formula. Type this formula:

Gridvalueat(currentrowindex,currentcolumnindex-1,currentsummaryindex) - Gridvalueat(currentrowindex,currentcolumnindex-2,currentsummaryindex)

The Crosstab would now display the difference between the two dates.

Let me know how this goes!

-Abhilash

Former Member
0 Kudos

Abhilash,

Thank you so much! That works perfectly. Now if I wanted to show the difference in a separate formula within the body of a report, i.e., the detail section, could that be possible?

Thanks,

Sumit.

Answers (0)