topic Re: Moving Average & its total in QlikView

Moving Average & its total

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

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

JUL

6,800,000

2,500,000

500,000

AUG

6,900,000

2,800,000

650,000

SEP

7,200,000

3,200,000

750,000

JUL

2,800,000

850,000

(125,000)

AUG

3,200,000

900,000

(100,000)

SEP

3,100,000

925,000

(75,000)

JUL

1,400,000

450,000

125,000

AUG

1,500,000

550,000

150,000

BR_CODE	CLAIMS
RA	3,500,000
EH	1,500,000
K7	750,000

SEP

1,600,000

600,000

100,000

Re: Moving Average & its total

sunny_talwar — Wed, 01 Nov 2017 17:55:31 GMT

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

Re: Moving Average & its total

nevilledhamsiri — Wed, 01 Nov 2017 18:00:32 GMT

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

Re: Moving Average & its total

sunny_talwar — Wed, 01 Nov 2017 18:05:25 GMT

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

Re: Moving Average & its total

nevilledhamsiri — Wed, 01 Nov 2017 18:14:37 GMT

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!

Re: Moving Average & its total

sunny_talwar — Wed, 01 Nov 2017 18:17:07 GMT

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

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

Re: Moving Average & its total

nevilledhamsiri — Wed, 01 Nov 2017 18:20:59 GMT

Thanks sunny, It works

Neville

Re: Moving Average & its total

sunny_talwar — Wed, 01 Nov 2017 18:32:51 GMT

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

Re: Moving Average & its total

nevilledhamsiri — Wed, 01 Nov 2017 18:52:49 GMT

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.

Re: Moving Average & its total

sunny_talwar — Wed, 01 Nov 2017 19:01:28 GMT

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

Re: Moving Average & its total

sunny_talwar — Wed, 01 Nov 2017 19:11:17 GMT

Thank you sir