Avg number of records based on different variables or selection

[Anonymous]

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.

[jwjackso]

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

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

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

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

[Anonymous]

Jerry thanks for the reply and i need to provide more detail. one attribute only contains a year value there are no months. my data set has 30K records and say year 2017 is selected then there's 15K records. what i need is for these 15K records to be avg over the 12 months since i only selected 2017. for this i am looking for 1,250 for a monthly avg over the single year i selected

if i select years 2016, 2017 then i get 24K records and would need that to be avg over 24 months. or in this example 1K

if i select 2018 i get 9K records and need that to be avg across the full months so until july 1 it would be avg over months.

i hope this add more clarity on what i'm looking to do.

[jwjackso]

The simplest idea that I have is to create an inline table:

MthCnt:

load * inline [

Year,MthCnt

2016,12

2017,12

2018,6

];

Then your expression is Sum(Amt)/Sum(MthCnt)

[Anonymous]

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

[jwjackso]

I think you will still need the inline table that I suggested since you don't have the number of months for the current year.

"Monthly Avg" will be =(Sum(Actions)/Sum(MthCnt)

"5 year avg" will be =(Sum({<Year={'2013','2014','2015','2016','2017'}>}Actions)/Sum({<Year={'2013','2014','2015','2016','2017'}>}MthCnt)