2 Replies Latest reply on Sep 6, 2018 12:48 PM by Jim Dehner

    How do I calculate a value that stays constant

    Jennifer Hysuick

      I am trying to determine how to create a calculated field that is a constant number.

       

      Here is my scenario:

      I have like 10,000 records (they are student records), and of those, I have 3,000 records with a withdrawal status. I am working on multiple worksheets where I report those withdrawals by school, Program, Month, withdrawal reason. I want to have the 3,000 number as the constant, so that when I'm trying to do percentages, it is #/3,000 to give me the %.  But anytime I bring in my number of leaves calculation, that 3,000 is also getting filtered and my % is always 100%.

       

      When I put them in a pie chart, I would like to see the % of 3,000 in any given slice, but I don't know how to do that calculation.

       

      I was thinking it should be a Fixed LOD, but I"m not sure what to "fix" it to so it is the same value on all worksheets, regardless of filter.

       

      Any help?

        • 1. Re: How do I calculate a value that stays constant
          Naveen B

          HI Jennifer

           

          Create a calculation like below

           

          {SUM(If [status]="Withdrawn" then [number of records] END)}

           

          the above calculation value wont change

           

          Hope this helps

           

          Pl mark this answer as correct.helpful to close the thread

           

          BR,

          NB

          • 2. Re: How do I calculate a value that stays constant
            Jim Dehner

            Hi Jennifer

            This is a case where nested  LOD's  will   fix  a value and it can be used across all worksheets -

            using a superstore analogy -  the inner lod looks at each order id to see if it has furniture the outer LOD sums up the individual Order IDs that meets the criteria and fixes it across the entire data base

             

             

            see

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.