1 Reply Latest reply on Mar 6, 2016 5:43 AM by Yuriy Fal

    Table calculations

    Eshwar Prasad

      Hi Team,

       

      Thanks in advance for your support.

       

      My requirement is like i need to get 5% Members.

      i did as below. Customer names sorted based on the sales in desc order.

      calculated 5% members like (5/100)*countd(Customername). i got 170.

      now i need to get that 170 members sales amount.

       

      To get that i tried like below.

      index()<=(5/100)*countd(Customername) will give boolean result then i can filter with true.. so that only top 170 rows will be remained.

      But i am not getting true value when i am trying to filter.

       

      And then i used windows_Sum(Sum(Sales)). But its not giving Proper result.

       

      I tried in this way but i am not able to get result. am i missing anything here?

      why windows_Sum(Sum(Sales)) is giving wrong result?

       

      if windows_Sum(Sum(Sales)) would give proper result can use this value in other views?

       

      Can any one help me to get this requirement..

       

      PFA for you reference.

        • 1. Re: Table calculations
          Yuriy Fal

          Hi Eshwar,

           

          Your original [5% Members] aggregate calculation

          gives different results depending on a view granularity.

           

          When no other dimensions are on a view

          (besides Measure Names),

          then your [5% Members] gives 170.

           

          When [Customer Name] dimension is on a view,

          then your [5% Members] gives 0 (see Sheet 1),

          and all subsequent calculations using [5% Members]

          could not give the results anticipated.

           

          To get it right one could calculate [5% Members]

          at the level of details excluding [Customer Name].

           

          Please find the attached wb w/mods.

           

          Hope it could help.

           

          Yours,

          Yuri