Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
];
Jay,
AVG:
LOAD
SUM(Amount)/2 as Avg
RESIDENT T
WHERE month(DATE)=month(today()) OR month(DATE)=month(addmonth(today(),-1))
Regards,
Michael
Hi Michael,
Though your solution work, I cant restrict the data set with WHERE condition.Can you give some other ideas?
-Jay
Jay, Can you be a little more specific? Not clear how your result table should look like.
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.
For me this is the right way.
Try this
Load (previous ([Month Amount ]) + [Month Amount])/2 as Month Amount ,* from Data source;