4 Replies Latest reply on Oct 13, 2017 4:46 AM by Jim Dehner

    How to calculate based on Dimension?

    Doug Krehbel

      This is driving me insane so thanks in advance. I have a very simple table:

      So I want to add a calculation: for Non PPD levels, I want to calculate the number of unique donors / 1500, for PPD, want to divide by 500.

       

      Something like this:

      {FIXED [Coordinator_Level]: [UniqueDonors]} / 500

       

      Except I want the 500 to be different based on the Coordinator_Level. EVERYTHING I've tried runs up against an error.

       

      Any ideas? I know this is probably simple.

        • 1. Re: How to calculate based on Dimension?
          Jim Dehner

          Hi Doug

           

          You need to include a qualifier in your equation - 2 ways - one using and IF - Then combination - the other is hardcoding the calc

          in the hard coded version try something like {fixed [coodinator_level]='CHICAGO Non PPD] : sum(unique doners)/500  }

           

          You may need to play with the syntax but that is the idea

           

          Using an if works also If [coodinator_level]='CHICAGO Non PPD]  then {fixed ='CHICAGO Non PPD] : sum(unique doners)/500  }

          else {fixed ='CHICAGO Non PPD] : sum(unique doners)/1500  } end

           

          Once again you may need to play with syntax

          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.

          • 2. Re: How to calculate based on Dimension?
            Deepak Rai

            If this does n't work, please provide few rows of fake data in Excel.

            {FIXED ([Coordinator Level]="Non PPD") :SUM(Unique donors)}/150

             

             

            and Second =n would be

            {FIXED ([Coordinator Level]=" PPD") :SUM(Unique donors)}/50

            Thanks

            Deepak

            • 3. Re: How to calculate based on Dimension?
              Doug Krehbel

              Thanks for trying but this doesn't work - it doesn't solve the issue of having one calculation column with values that adjust based on one of the row dimensions. Maybe wasn't clear - for each row, I need that column calculation to divide by a different number based on the coordinator value -

              • 4. Re: How to calculate based on Dimension?
                Jim Dehner

                No that was't clear - at least for me -

                 

                the form of the solution will be   {fixed  [coordinator level] :    sum( if contains(min([condinator level]),"PPD" then  sum([unique donors])/ 500        else   sum([unique donors]/1500   end )}   you may need to play with the aggregation issue on the coordinator level and the if statement -

                 

                Not clear are the 500 and 1500 the only values and arre they fixed or are they dependent on something else?

                 

                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.