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.

[sunny_talwar]

See if this helps:

Script:

MonthlyQty:

LOAD *,

  Date(EndOfMonthDate) as Date

INLINE [

Thing, FinMonth, EndOfMonthDate, Qty

A, 201507, '7/31/2015',20

A, 201506, '6/30/2015',20

A, 201505, '5/30/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/30/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/30/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/30/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/30/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

];

Expression:

=Avg({<Date = {"$(='>' & Date(AddMonths(Max(Date),-$(vVar))) & '<=' & Date(Max(Date)))"}, FinMonth = >}Qty)

Where vVar is a variable controlled by the slider object.

HTH

Best,

Sunny

[Anonymous]

You shouldn't delete. Good example of a set analysis without need to use variables.

BR

Serhan

[#4]

Thank you. That's closer, but still not quite right. For instance, if I choose 201502 with a 1 month average, it's still averaging over two months. I'm playing around with it to see if I can fix it.

[sunny_talwar]

Try this expression:

=Sum({<Date = {"$(=If($(vVar)= 1, '>' & Date(MonthStart(Max(Date))) & '<=' & Date(Max(Date)), '>' & Date(AddMonths(Max(Date),-($(vVar)-1))) & '<=' & Date(Max(Date))))"}, FinMonth = >}Qty)

[Anonymous]

Hello Brian,

Did you check my post? I believe I hit it correctly even on the first post

Any problems on that?

BR

Serhan

[#7]

You're right. I missed it amidst some of the other posts, and it worked all along. I'll give it a try in my application.

Thanks to everyone for taking the time...

[#8]

I'm curious though -- in your definition of vMaxMonth, is there any reason you use 2 for the day value? :

=date(makedate(left(max(FinMonth),4),right(max(FinMonth),   2   )),'YYYYMM')

[Anonymous]

Hello,

2 is for right() function, not for makedate().

BR

Serhan

[#10]

Of course. I see that now. Thanks again.