on 06-18-2012 1:22 PM
catagory | year | Jan | Feb | Mar | Apr | Total |
---|---|---|---|---|---|---|
conditioner | 2010 | 2 | 2 | 2 | 2 | 8 |
Quotient 8:12 | ||||||
conditioner | 2011 | 3 | 3 | 3 | 3 | 12 |
Quotient 12:16 | ||||||
conditioner | 2012 | 4 | 4 | 4 | 4 | 16 |
Shampoo | 2010 | 2 | 2 | 2 | 2 | 8 |
Quotient 8:12 | ||||||
Shampoo | 2011 | 3 | 3 | 3 | 3 | 12 |
Quotient 12:16 | ||||||
Shampoo | 2012 | 4 | 4 | 4 | 4 | 16 |
Cream | 2010 | 2 | 2 | 2 | 2 | 8 |
Quotient 8:12 | ||||||
Cream | 2011 | 3 | 3 | 3 | 3 | 12 |
Quotient 12:16 | ||||||
Cream | 2012 | 4 | 4 | 4 | 4 | 16 |
Hey,
Above is an example of my table, I tried to do a quotient between the years, the problem is that it doesn't do a quotient for all the lines just for one catagory.
How do I make it open a quotient aoutomaticlly for all the lines?
and how to do it only for the totals? becuase it's not doing a quotient for the totals, but only for the months.
Thanks,
Heli
H iHeli,
The above Cross tab is display as you desired.
You did the quotient per year and it is correct. Can you try using Category instead of Year?.
Thanks
Jothi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
It's still gives me an orror Out of Range,
look at this post
http://scn.sap.com/message/13338945#13338945
How would you have done it?
Thanks
Heli
Hi Heli,
Can you try checking if it isDiving by Zero..
if (
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(2010.00), CurrentSummaryIndex) <> 0 and
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(2011.00), CurrentSummaryIndex) <> 0 ) then
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(2010.00), CurrentSummaryIndex) /
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf(2011.00), CurrentSummaryIndex)
When it comes to Gridvalue at Outofrange then The column you are specifying as column is not present in the grid. Use the correct value of the column name in Grid formula.
Here is the Thread for your ref.
http://scn.sap.com/thread/1832101
Please check once again.
Thanks
Jothi
If
year(Gridlabelat("TOD_Master_sheet_For_Sap_Crystal_Logistic;1.DocDate", currentrowindex)) = ({@CurDate}) //** (2012)**
and (GridValueAt (CurrentRowIndex, currentcolumnindex+2, CurrentSummaryIndex)) <> 0
then
(GridValueAt (CurrentRowIndex, currentcolumnindex+2, CurrentSummaryIndex)) /
(
if year(Gridlabelat("TOD_Master_sheet_For_Sap_Crystal_Logistic;1.DocDate", currentrowindex)) = ({@2011})
and (GridValueAt (CurrentRowIndex, currentcolumnindex+2, CurrentSummaryIndex)) = 0
then
0
else
(GridValueAt (CurrentRowIndex-1, currentcolumnindex+2, CurrentSummaryIndex))
)
else (
If year(Gridlabelat("TOD_Master_sheet_For_Sap_Crystal_Logistic;1.DocDate", currentrowindex)) = ({@2011})
and (GridValueAt (CurrentRowIndex, currentcolumnindex+2, CurrentSummaryIndex)) <> 0
then
(GridValueAt (CurrentRowIndex, currentcolumnindex+2, CurrentSummaryIndex)) /
(
if year(Gridlabelat("TOD_Master_sheet_For_Sap_Crystal_Logistic;1.DocDate", currentrowindex)) = ({@2010})
and (GridValueAt (CurrentRowIndex, currentcolumnindex+2, CurrentSummaryIndex)) = 0
then
0
else
(GridValueAt (CurrentRowIndex-1, currentcolumnindex+2, CurrentSummaryIndex))
)
else 0
)
So this is my solution that works, but I still have 1 problem, if some customer doesn't have data in 2012 it is out of range.
How can I use "minimum" year for that?
If minimum year <> 2012 then don't divide or =0
I can't find a syntax for it..
Thanks
Heli
Actually it's :
If 2012 <> 0 then (my formula)
else 0
But I see I have another problem, with my avarage : http://scn.sap.com/message/13296915
when I do the sum
let's say a customer does not have sales on january
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jan"), CurrentSummaryIndex)+
it's gives me out of range.
Is there a way to tell it if soome month don't exist ignore it?
I tried doing
if
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jan"), CurrentSummaryIndex) = 0
but it's still out of range.
Do you have a solution for me?
Thanks
Heli
Hi Heli,
Do you have 0's for Jan or just empty cell?.
if it then see if you can use "Isnull" or "null. May be the Jan month doesn;t have any value not even 0 assigned. so try checking iwht null values.
if
GridValueAt(CurrentRowIndex, GetColumnPathIndexOf("Jan"), CurrentSummaryIndex) = " "
Thanks,
Jothi
Hey Jothi,
I really need help here..
I have those 3 years, not all of my items was sold in 2011.
my formulla says 2012/2011
but for some items the row 2011 doesn't exist.
How can I tell the Crystal to ignore the row below if it doesn't exist (or devide it by 1 or whatever..) ?
If 2012 <> 0 then
(
If year(Gridlabelat("TOD_Master_sheet_For_Sap_Crystal_Logistic;1.DocDate", currentrowindex)) = ({@CurDate})
and (GridValueAt (CurrentRowIndex, currentcolumnindex+2, CurrentSummaryIndex)) <> 0
then
(GridValueAt (CurrentRowIndex, currentcolumnindex+2, CurrentSummaryIndex)) /
(
if year(Gridlabelat("TOD_Master_sheet_For_Sap_Crystal_Logistic;1.DocDate", currentrowindex)) = ({@2011})
and (GridValueAt (CurrentRowIndex-1, currentcolumnindex+2, CurrentSummaryIndex)) = 0
then
0
else
(GridValueAt (CurrentRowIndex-1, currentcolumnindex+2, CurrentSummaryIndex))
)
else 0
CurrentRowIndex-1 Dosn't exist for somr items..
Thanks,
Heli
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
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.