2 Replies Latest reply on Aug 22, 2018 10:33 AM by susheela.hegde

    Average Value Display

    susheela.hegde

      Hi All,

       

      I had created a below  thread to ignore Weekend Value and get the count.

       

      How to find the Average ignoring Week End data also how to hide the parameter

       

       

      I did get this work  but i am stuck with how to display the Over all average value along with certain filter.

      I did not want to continue in the same thread and started new one to avoid confusion.

       

      Scenario:

      1. I have to calculate  Over all Average value daily, weekly, monthly etc . This is fixed.

      2. Display daily, weekly. monthly value when we change the parameters.

      3. I want a scorecard which compares Actual Value with the Average (sheet-With both the Values).

       

      I am not able to display actual and Over all Average in the same sheet and have to create two individual sheets.

      Is there a way to show Actual vs Average in the same sheet?

        • 1. Re: Average Value Display
          Patrick A Van Der Hyde

          Hello Chits,

           

          I believe your calclulation for the filter is incorrect and cutting off weeks, months and years.

           

          Try the calculation below instead and see how I ahve adjusted the less than value to NOT be less than or equal but instead less than the next week, month, quarter or year.  Your datetrunc() command makes the math for each of these work because the starting day to add to is always the first day of a week, month, quarter, or year and so the addition values will allow us to ignore any sort of logic to deal with shorter months (February), Leap years, etc..   I hope this helps. 

           

          If [Select Period]='yesterday'
          then
          IF [Order Date]=(TODAY()-1) THEN TRUE else FALSE end
          
          
          ELSEIF  [Select Period] = 'day'
          
          
          then
            if [Order Date] = [Date Selection] then TRUE else FALSE end
          
          
          ELSEIF  [Select Period] = 'week'
          THEN
            if [Order Date] >= DATETRUNC('week',[Date Selection]) and [Order Date] < DATETRUNC('week',[Date Selection]+7) then
            TRUE else FALSE end
          ELSEIF  [Select Period] = 'month'
          THEN
            if [Order Date] >= DATETRUNC('month',[Date Selection]) and [Order Date] < DATETRUNC('month',[Date Selection]+31) then
            TRUE else FALSE end
          
          
          ELSEIF  [Select Period] = 'quarter'
          THEN
            if [Order Date] >= DATETRUNC('quarter',[Date Selection]) and [Order Date] < DATETRUNC('quarter',[Date Selection]+95) then
            TRUE else FALSE end
          
          
          ELSEIF  [Select Period] = 'year'
          THEN
            if [Order Date] >= DATETRUNC('year',[Date Selection]) and [Order Date] < DATETRUNC('year',[Date Selection]+367) then
            TRUE else FALSE end
          END
          

           

           

          Patrick 

          • 2. Re: Average Value Display
            susheela.hegde

            Patrick,

             

            Thank you very much for the suggestion.

             

            Do you have any thoughts on how to display the averages in scorecard?