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
I added another column, I want to do in this column a percent change between 2010-2011 and 2011-2012
The formula suppose to look like this
(2012-2011)/2011
(2011-2010/)2010
How do you suggest doing it?
Heli
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
Did you tried Embedded Summary instead a Calculater memeber and you still face this error?
Thanks
Jothi
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
Hi Heli,
Sorry for the late response,
Glad you got the solution.
Use another IF condition to check Zeros and then print the output
If 2012 <> 0 then divide
else if
your formula.
Thanks
Jothi
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
Jan does noe exist in this customer record,
I tried = 0 or NULL or ISNULL
non of them work, I'm still getting errors
Heli
Can you try "If NextIsNull() then" and also try "if " " then"... Just an empty quote rather giving Null ir ISnull or 0.
Use <> sign for comparision and ask the condition to execute.
Let me know if you still facing the issue.
Thanks,
Jothi
HI Heli,
Did you solve the issue ?.
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
Hi Heli,
Did you tried removing Currentrowindex -1 and just keep currerntrow index?.
Thanks,
Jothi
Hi Jothi,
Yes I did, but then it divides by it self and give me 1..
I need to do Currentrowindex -1.
But I need some condition that says "if Currentrowindex -1 not exist ignore it" or divide by 1 or whatever..
Just so it won't give me "out of range".
Thanks,
Heli