16 Replies Latest reply: Sep 6, 2012 9:55 AM by Heli Swissa

Calculated member - Rows Quotient

Currently Being Moderated
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

• Re: Calculated member - Rows Quotient
Currently Being Moderated

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

• Re: Calculated member - Rows Quotient
Currently Being Moderated

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

• Re: Calculated member - Rows Quotient
Currently Being Moderated
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
• Re: Calculated member - Rows Quotient
Currently Being Moderated

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

• Re: Calculated member - Rows Quotient
Currently Being Moderated

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

Thanks

Jothi

• Re: Calculated member - Rows Quotient
Currently Being Moderated

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.

Thanks

Jothi

• Re: Calculated member - Rows Quotient
Currently Being Moderated
```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

• Re: Calculated member - Rows Quotient
Currently Being Moderated

Hi Heli,

Sorry for the late response,

Use another IF condition to check Zeros and then print the output

If 2012 <> 0 then divide

else if

Thanks

Jothi

• Re: Calculated member - Rows Quotient
Currently Being Moderated

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

• Re: Calculated member - Rows Quotient
Currently Being Moderated

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

• Re: Calculated member - Rows Quotient
Currently Being Moderated

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

• Re: Calculated member - Rows Quotient
Currently Being Moderated

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

• Re: Calculated member - Rows Quotient
Currently Being Moderated

HI Heli,

Did you solve the issue ?.

thanks,

Jothi

• Re: Calculated member - Rows Quotient
Currently Being Moderated

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

• Re: Calculated member - Rows Quotient
Currently Being Moderated

Hi Heli,

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

Thanks,

Jothi

• Re: Calculated member - Rows Quotient
Currently Being Moderated

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