cancel
Showing results for 
Search instead for 
Did you mean: 

Calculated member - Rows Quotient

Former Member
0 Kudos
catagory
year JanFebMarAprTotal
conditioner201022228






Quotient 8:12
conditioner2011333312






Quotient 12:16
conditioner2012444416
Shampoo201022228






Quotient 8:12
Shampoo2011333312






Quotient 12:16
Shampoo2012444416
Cream201022228






Quotient 8:12
Cream2011333312






Quotient 12:16
Cream2012444416


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

Accepted Solutions (1)

Accepted Solutions (1)

jyothirmayee_s
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

jyothirmayee_s
Active Contributor
0 Kudos
Hi Heli,
If you think that Year column doesn;t change then use Embedded Summary instead of Calculater member.
Assume 1 as your 2011 and 2 as 2012....so on. Below is the formula.
GridValueAt (CurrentRowIndex, CurrentColumnIndex ,1 ) /
GridValueAt (CurrentRowIndex, CurrentColumnIndex ,2 )
Hope this helps,
Thanks,
Jothi
Former Member
0 Kudos

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

jyothirmayee_s
Active Contributor
0 Kudos

Did you tried Embedded Summary instead a Calculater memeber and you still face this error?

Thanks

Jothi

jyothirmayee_s
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

jyothirmayee_s
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

  1. then....

but it's still out of range.

Do you have a solution for me?

Thanks

Heli

jyothirmayee_s
Active Contributor
0 Kudos

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)  = " "

  1. then....

Thanks,

Jothi

Former Member
0 Kudos

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

jyothirmayee_s
Active Contributor
0 Kudos

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

jyothirmayee_s
Active Contributor
0 Kudos

HI Heli,

Did you solve the issue ?.

thanks,

Jothi

Former Member
0 Kudos

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

jyothirmayee_s
Active Contributor
0 Kudos

Hi Heli,

Did you tried removing Currentrowindex -1 and just keep currerntrow index?.

Thanks,

Jothi

Former Member
0 Kudos

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

Answers (0)