Moving Average & its total

[nevilledhamsiri]

Dear All!

Please check why the total wont come accurately over averages calculated. Excel used is also attached here.

Expression applied in calculation is as follows!

AVG(AGGR(SUM(PREMIUM),BR_CODE,MONTH))

AVG(AGGR(SUM(EXPENSES),BR_CODE,MONTH))

Thanks

Neville

BR_CODE	MONTH	PREMIUM	EXPENSES	INCOME
RA	JUL	6,800,000	2,500,000	500,000
RA	AUG	6,900,000	2,800,000	650,000
RA	SEP	7,200,000	3,200,000	750,000
EH	JUL	2,800,000	850,000	(125,000)
EH	AUG	3,200,000	900,000	(100,000)
EH	SEP	3,100,000	925,000	(75,000)
K7	JUL	1,400,000	450,000	125,000
K7	AUG	1,500,000	550,000	150,000
K7 BR_CODE CLAIMS RA          3,500,000 EH          1,500,000 K7              750,000	SEP	1,600,000	600,000	100,000

[sunny_talwar]

What are the expected numbers? Where is the Excel attached?

[nevilledhamsiri]

Hi, Sunny,

It is not attached rather presented in the body of the doc itself. Average considered from July to Sep which is shown in the excel with the formula used!

Thanks

Neville

[sunny_talwar]

68+69+72/3 = 69.67 which is what you got for RA.. what is the issue?

[nevilledhamsiri]

You are right but see the total of (EH+K7+RA)=3.8 which is wrong. It has to be 11.4. Though the individual Branch figure is ok, once all BR_codes are totaled up figure appears to be wrong!

[sunny_talwar · Accepted solution]

So, for the total you want to sum them? May be this

SUM(AGGR(AVG(AGGR(SUM(PREMIUM),BR_CODE,MONTH)), BR_CODE))

[nevilledhamsiri]

Thanks sunny, It works

Neville

[sunny_talwar]

Just don't understand how is this the correct answer?

Can you explain what in this response of your make it a correct response? If thanks was the answer to all your problems, then I can keep responding thanks to all your future posts

[nevilledhamsiri]

Very sorry, mistakenly I had chosen my feed back. Will be much more concern about this. Also please explain How I could mark your correct answer as ''correct answer' since I have incorrectly done my response.

[sunny_talwar]

Sir this is not the first time you have done this. I hate to not help people, but I have been making a conscious effort to not help people who are not a responsible citizen of this community. I thought may be its been a while and you might have learnt your lesson, but yet again you marked your own response as correct.

I am not hungry for points, but it is important that you mark the appropriate responses as correct as it help future visitors look for answers. Looking at OPs thank you is not very helpful for future visitors.

Please fix this and any of your previous threads by looking how to uncheck your response as correct and mark the appropriate responses as correct.

Qlik Community Tip: Marking Replies as Correct or Helpful