topic Re: AVG calulating in QlikView

AVG calulating

Mon, 09 Dec 2013 12:51:01 GMT

Hi All,

I am having following scenario.

In one of my table T, having fields as F1,F2,F3...etc Now I need to calculate AVG for one of the field.

Ex:

F1 having the AMOUNT ,I need to calculate (Previous Month Amount+ Current Month Amount)/2 in the SCRIPT level. (Not with chart)

Let me know with your ideas.

-Jay

Re: AVG calulating

Anonymous — Mon, 09 Dec 2013 13:01:53 GMT

You can first load a table with distinct yearmonth and the sales amount. Use a group by and order by to sort them in asc or desc order.

Then, use peek or previous function to get the previous month values in the current record and use your formula to calculate the number.

hope that helps.

You might also post a sample app to get a more clear solution from the community.

Regards,

Aadil

Re: AVG calulating

Mon, 09 Dec 2013 13:05:48 GMT

Hi Adil,

As I cant post my data set here,kindly consider the below table structure

LOAD * INLINE [DATE,Amount

12-10-2013,2500

10-11-2013,3500

09-12-2013,4500

];

Re: AVG calulating

Anonymous — Mon, 09 Dec 2013 13:11:38 GMT

Jay,

AVG:
LOAD
SUM(Amount)/2 as Avg
RESIDENT T
WHERE month(DATE)=month(today()) OR month(DATE)=month(addmonth(today(),-1))

Regards,
Michael

Re: AVG calulating

Mon, 09 Dec 2013 13:22:45 GMT

Hi Michael,

Though your solution work, I cant restrict the data set with WHERE condition.Can you give some other ideas?

-Jay

Re: AVG calulating

Anonymous — Mon, 09 Dec 2013 13:26:24 GMT

Jay, Can you be a little more specific? Not clear how your result table should look like.

Re: Re: AVG calulating

Anonymous — Mon, 09 Dec 2013 13:26:34 GMT

Check attached. Hope this is what you are looking for. IF you have the date or month already available, then you dont have to do that many loads. This is a quick solutions, you create the load statements carefully and have as minimum load as possible.

Re: AVG calulating

prodanov — Thu, 20 Mar 2014 13:43:57 GMT

For me this is the right way.

Re: AVG calulating

sunilkumarqv — Thu, 20 Mar 2014 13:48:07 GMT

Try this

Load (previous ([Month Amount ]) + [Month Amount])/2 as Month Amount ,* from Data source;