on 12-21-2011 12:38 AM
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.
Hi Sumit,
Could you provide us a sample of how the cross-tab looks with some data too.
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.