2 Replies Latest reply on Apr 25, 2013 7:25 AM by Jason Back

    Counting sum of value set by calculated field

    Jason Back

      Hello,

       

      I am trying to count the total # of stores that had sales value >=x. 

       

      Simple example is attached: what I want to have is a calculated field that returns value of 5 which in the example, the sum of stores.  So when I take out Store from Rows it would give me numeric value of 5 for East Division.

       

      If there is any suggestions, I would appreciate it.  Thank you.

      Jason

        • 1. Re: Counting sum of value set by calculated field
          Tracy Rodgers

          Hi Jason,

           

          One way to do this is to create a calculated field similar to the following:

           

          window_sum([Calculation1])

           

          Or it can be added to the grand total by creating the below calculated field:

           

          IF FIRST()==0 THEN

          WINDOW_SUM(([Calculation1]),0,IIF(FIRST()==0,LAST(),0))

          END

           

          Then, place this on the view. Right click on Store and select Duplicate. Then, place the Store (copy) on the level of detail shelf. Right click on the above calc and select Compute using...>Store (copy)

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Counting sum of value set by calculated field
            Jason Back

            Hi Tracy,

             

            Thank you for the answer.  That way certianly helps a lot.

             

            Sorry, I should have mentioned my final visual goal here. I wanted to visualize % of stores that had sales >=x for each division.

             

            So I actually had a calcuatied field that returns total # of stores. So I thought I could to make another calculatied field by division like this [# of stores sales >=x] / [total # of stores]. And I would just plug that into sheet with Year on the column and Division on row.

             

            I don't think any calculation using window function would work with that.. should I have approached this in different way from the begging?  Thank you.

             

            Jason