5 Replies Latest reply: Jun 14, 2018 9:53 AM by Jerry Jackson RSS

    Avg number of records based on different variables or selection

    Anthony Richardson

      i have one field [Year] that holds 4 values : 2018, 2017, 2016, 2015

      i have a data set with 15 fields that changes based on the selection of the Year or Years.

       

      what i'm trying to do is an average based on the selection(s) of the Year field. 

      if i select only 1 year then i need to count the number of records in the data set and divide by 12

       

      if i select more that one year i need the value to average over the respective months, 1=12, 2=24, 3=36, 4=48, and 6=50

       

      any suggestions on writing this expression ? i think i can write the if statements, but i'm trying to figure out how to pass in the selection from the filter [Year] field.

        • Re: Avg number of records based on different variables or selection
          Jerry Jackson

          What do you date fields look like.  I created a simple data set with a MthYr to distinguish Jan 2017 from Jan 2018.  The avg function seems to work fine.

           

          If no year is selected, then I get Sum(Amt for 2017 and 2018) / 18

          AVG1.PNG

           

          If 2017 is selected, then I get Sum(Amt 2017) / 12

           

          AVG3.PNG

           

          If 2018 is selected, then I get Sum(Amt for 2018)/6

           

          AVG2.PNG

           

          If I select 2017 and Jan 2018, I get (Sum(Amt for 2017) + Jan 2018)/13

           

          AVG4.PNG

          • Re: Avg number of records based on different variables or selection
            Anthony Richardson

            ok let me see if i can articulate my requirement better.

            i have 1 field "Year" - it holds 2013, 2014,2015,2016,2017, 2018

             

            i have another field "actions" this is a count of records in my data set. 

             

            i have another field "Montly Avg"

            I have another field "5 year avg"

             

            i need an expression that calculates the avg based on "year" if i select 1 or more years i need the number of "actions" to be avg.

             

            For example if i select "year" = 2017, that has 1200 records in it the "monthly avg" should be 100

            If i select 2017, and 2016, that has 4800 records in it and the "monthly avg" should be 200

             

            example for the "5 year avg" i need it to add all records from 2013-2017 and avg them by month, so i'm assuming get the total records from "actions" and divide by 60

             

            any help would be greatly appreicated