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

Calculated member - Rows Quotient

Heli Swissa
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
    Jyothirmayee S
    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
      Heli Swissa
      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
        Jyothirmayee S
        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
          Heli Swissa
          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
            Jyothirmayee S
            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
            Jyothirmayee S
            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.

             

            Here is the Thread for your ref.

            http://scn.sap.com/thread/1832101

             

            Please check once again.

             

            Thanks

            Jothi

            • Re: Calculated member - Rows Quotient
              Heli Swissa
              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

Actions