topic Re: X Month Average Quantity in QlikView

X Month Average Quantity

Mon, 20 Jul 2015 14:23:21 GMT

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.

Re: X Month Average Quantity

Anonymous — Mon, 20 Jul 2015 14:47:12 GMT

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

Basically you need to use set analysis and variables.

Serhan

Re: X Month Average Quantity

stigchel — Mon, 20 Jul 2015 14:48:03 GMT

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

Re: X Month Average Quantity

sunny_talwar — Mon, 20 Jul 2015 14:48:20 GMT

Try this:

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

Re: X Month Average Quantity

stigchel — Mon, 20 Jul 2015 14:55:14 GMT

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

Re: X Month Average Quantity

sunny_talwar — Mon, 20 Jul 2015 14:58:37 GMT

Hahahaha right

Re: X Month Average Quantity

Anonymous — Mon, 20 Jul 2015 14:58:46 GMT

Gurus,

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

Serhan

Re: X Month Average Quantity

stigchel — Mon, 20 Jul 2015 15:15:23 GMT

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

Re: X Month Average Quantity

sunny_talwar — Mon, 20 Jul 2015 15:27:43 GMT

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.

Re: X Month Average Quantity

Mon, 20 Jul 2015 15:28:17 GMT

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
];

Re: X Month Average Quantity

sunny_talwar — Mon, 20 Jul 2015 15:43:45 GMT

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

Re: X Month Average Quantity

Anonymous — Mon, 20 Jul 2015 19:51:55 GMT

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

Serhan

Re: X Month Average Quantity

Tue, 21 Jul 2015 14:12:06 GMT

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.

Re: X Month Average Quantity

sunny_talwar — Tue, 21 Jul 2015 14:25:48 GMT

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)

Re: X Month Average Quantity

Anonymous — Tue, 21 Jul 2015 14:39:09 GMT

Hello Brian,

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

Any problems on that?

Serhan

Re: X Month Average Quantity

Tue, 21 Jul 2015 14:55:09 GMT

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...

Re: X Month Average Quantity

Tue, 21 Jul 2015 15:00:10 GMT

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')

Re: X Month Average Quantity

Anonymous — Tue, 21 Jul 2015 15:28:31 GMT

Hello,

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

Serhan

Re: X Month Average Quantity

Tue, 21 Jul 2015 15:34:21 GMT

Of course. I see that now. Thanks again.