1 Reply Latest reply on May 3, 2018 4:05 PM by Patrick A Van Der Hyde

    Blanket Calculation Across Any and All Measures on a View

    Mark Alvarado

      So I am being tasked to create a calculation that looks at the previous 3 periods summed average vs. the current period we are in and produce a % change showing if in the current period we are above or below the previous 3 periods summed average.


      My problem is i am having a hard time creating a "blanket' calculation that will calculate all measures.  Is this even possible?  so on the below view i would like a fifth column to be displayed that shows our current 5 change to the previous 3 periods...


      And another wrench in the problem is to be able to determine how many weeks are included in the current period so i can calculate the current periods expected totals.  For Example the view below shows 3 out of the 4 weeks included in the current period.  so i need to be able to divide each measure by (3/4) to get the expected periods totals.


        • 1. Re: Blanket Calculation Across Any and All Measures on a View
          Patrick A Van Der Hyde

          Hello Mark,


          You could create a custom grand total calculation to control the output of each grand total field (not in this view) and it can be defined as desired.  Every measure would need to be setup to have a if then end statement that identifies the total column from the other columns.   Jonathan Drummey has a great post about this sort of thing here - Customizing Grand Totals – Part 2 | Drawing with Numbers  


          You could control which columns will be used across all your calculations with a parameter if you needed to change the 5 and the 3 in the example above. 


          It's going to depend if this is a one off report or an ongoing report that changes to determine how much effort goes into building a view.  The easier solution is just to create another view with the exact calculation desired and then put this into a dashboard next to measures in this view.   That view would not include any dimension desired in a total.


          I hope that helps and best of luck