X Month Average Quantity

[#1]

I need help writing an expression. I can get what I want using brute force, but I think there must be a cleaner, more efficient way. Please see attached. I'm trying to include a column value in my chart which represents the X Month Average Quantity -- i.e. the average quantity over the last X months from the date selected, where X is the value given by the slider. The average should start with the month/year selected. So if 201505 is selected, and 3 is selected from the slider, then it should average the quantities from 201505, 201504 and 201503.

Thanks for taking a look.

[Anonymous · Accepted solution]

Check this one out. Did not check the numbers deliberately but should be fine.

Basically you need to use set analysis and variables.

BR

Serhan

[stigchel]

You can use set analysis in combination with dollar sign expansion for that (see help). Something like

=Avg({<FinMonth={">$(=FinMonth-NoMonths)<=$(=Max(FinMonth))"}>} Qty)

Also see attached

[sunny_talwar]

Try this:

=Avg({<FinMonth = {"$(='<=' & Max(FinMonth) & '>' & (Max(FinMonth)-Only(NoMonths)))"}>}Qty)

[stigchel]

Glad to see you did the same (I edited my answer), but I think the '>=' & (Max( should be '>' & (Max(F ?

[sunny_talwar]

Hahahaha right

[Anonymous]

Gurus,

With all respect, I think you are missing a twist in year changes Finmonth should be handled as date instead of number.

BR

Serhan

[stigchel]

You are right, too hasty in my reply and not thinking past the example given

[sunny_talwar]

Once again agreed . I feel I should just delete my response so that I don't look dumb

Oh well, I am going to leave it still so that other people learn to not make the same mistake I did.

[#10]

Thanks everyone. These are good ideas, but, as mentioned, it breaks when the year changes. My "actual" solution has a corresponding real date value that I can use -- so 201505 has a corresponding field called EndOfMonthDate with value '5/31/2015 12:00:00 AM'. Here's an updated script excerpt of the MonthlyQty table. Hopefully I formatted the dates correctly.

MonthlyQty:
LOAD * INLINE [Thing, FinMonth, EndOfMonthDate, Qty
A, 201507, '7/31/2015',20
A, 201506, '6/30/2015',20
A, 201505, '5/31/2015',18
A, 201504, '4/30/2015',16
A, 201503, '3/31/2015',12
A, 201502, '2/28/2015',30
A, 201501, '1/31/2015',27
A, 201412, '12/31/2014',25
B, 201507, '7/31/2015',400
B, 201506, '6/30/2015',420
B, 201505, '5/31/2015',440
B, 201504, '4/30/2015',380
B, 201503, '3/31/2015',327
B, 201502, '2/28/2015',315
B, 201501, '1/31/2015',290
B, 201412, '12/31/2014',0
C, 201507, '7/31/2015',7
C, 201506, '6/30/2015',6
C, 201505, '5/31/2015',6
C, 201504, '4/30/2015',6
C, 201503, '3/31/2015',6
C, 201502, '2/28/2015',6
C, 201501, '1/31/2015',6
C, 201412, '12/31/2014',6
D, 201507, '7/31/2015',100
D, 201506, '6/30/2015',131
D, 201505, '5/31/2015',131
D, 201504, '4/30/2015',140
D, 201503, '3/31/2015',141
D, 201502, '2/28/2015',141
D, 201501, '1/31/2015',141
D, 201412, '12/31/2014',128
E, 201507, '7/31/2015',0
E, 201506, '6/30/2015',0
E, 201505, '5/31/2015',1
E, 201504, '4/30/2015',1
E, 201503, '3/31/2015',3
E, 201502, '2/28/2015',5
E, 201501, '1/31/2015',5
E, 201412, '12/31/2014',3
];