topic Help needed with rolling (cumulative)AVG in QlikView

Help needed with rolling (cumulative)AVG

Mon, 07 Jun 2010 22:35:04 GMT

Hi everyone

Can someone help me do this operation?

the month is a dimension that it could be anything (year, week....)

and it is a range of months that i pick from my filtres "sheet" so it is not something static

Shall i use a pivot or a single table? and i have no problem if the calculations take place horizontal or Vertical .

I just want to do them [:P]

This is one dimension Count = 1

Count = 2

Count = 3

Count = 4

Count = 5

Count = 6

Month

Oct09

Nov09

Dec09

Jan10

Feb10

Mar10

Sum(Sales)

AVG

Sum(Sales)

AVG

Sum(Sales)

AVG

Sum(Sales)

AVG

Sum(Sales)

AVG

Sum(Sales)

AVG

CustA

47,5

61,33

57,25

48,80

48,17

CustB

37,5

43,33

46,25

42,00

48,00

CustC

29,33

38,25

37,60

42,17

This is the SUM of (10+28+50)/3

This is the SUM of (35+65+10+28+50)/5

This is the SUM of (35+65+10+28+50+65)/6

THANK YOU VERY MUCH

Help needed with rolling (cumulative)AVG

Mon, 07 Jun 2010 23:46:00 GMT

You've essentially described a pivot table.

Is there some part of the pivot table implementation you need help with?

Help needed with rolling (cumulative)AVG

Mon, 07 Jun 2010 23:50:57 GMT

I just re-read your post, and I missed the 'cumulative' part of the request.

You could achieve the cumulative average using set analysis, and descriving the average calculation as the average of all sales for everything in the selection described as current and prior to, as opposed to the default functionality wich is to average only those fact values that meet both dimension criteria.

However, this won't actually do exactly what you've described. As opposed to the averages of the totals, you'll actually get the average at the individual sales level.

Help needed with rolling (cumulative)AVG

Tue, 08 Jun 2010 07:59:59 GMT

Hi and thank you for the interest to help.

I have 1 question. As you mentioned the "prior to" and "current" i assume that is something like the "before" and "after" statements?

Is there any statement that collects the SUM of columns dimensions (before or after) instead than the previous only or next?

and if not is there any possibility to somehow do a smart Loop operation to gather these values?

Help needed with rolling (cumulative)AVG

Tue, 08 Jun 2010 10:15:32 GMT

i found one nice trick so far. in order to get the cumulative count of "Dimensions" we can use the columnno(TOTAL)

Now what is left is to get the cumulative sum of sales through dimensions

Help needed with rolling (cumulative)AVG

Tue, 08 Jun 2010 11:02:26 GMT

My friends thank you for the answer I found it

and it is working perfectly

rangesum(before(SUM([SALES]),0,columnno(TOTAL)))/columnno(TOTAL)