Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have the following table
[Sales]:
[
Year | Client | Sales
2010,Corporate,1000
2010,Retail,250
2011,...
.
.
.
2016,...
]
I would like to compute the average sales between 2 consecutive years.I tried doing so with the following script but it didn't work.
for each a in FieldValueList('Year')
[prep average]:
LOAD $(a)-1 as a_avg,Client as Cl_avg,sum(if(Year=$(a)-1,Sales,0)) as Sales_a_1
Resident [Sales]
Group by a_avg,Cl_avg
JOIN([prep average])
LOAD $(a) as a_avg,Client as Cl_avg ,sum(if(Year=$(a),Sales,0)) as Sales_a
Resident [Sales]
Group by a_avg,Cl_avg
next a
[average]:
LOAD a_avg,Cl_avg,(Sales_a_1+Sales_a)/2 as average
Resident [prep average];
I obtained a table where Sales_a_1 and Sales_a are shifted for the same year so I got nothing in the field average.
Thank you in advance for your help
Are you looking to get this?
Try this
[Sales]:
LOAD * inline [
Year,Client,Sales
2010,Corporate,1000
2010,Retail,250
2011,Corporate,1050
2011,Retail,300
2012,Corporate,1025
2012,Retail,305
2013,Corporate,1047
2013,Retail,312
2014,Corporate,1068
2014,Retail,400
2015,Corporate,1071
2015,Retail,403
2016,Corporate,1083
2016,Retail,407
];
FinalSales:
LOAD *,
If(Client = Previous(Client), RangeAvg(Sales, Previous(Sales))) as AvgSales
Resident Sales
Order By Client, Year;
DROP Table Sales;
Will you able to provide sample output for given data set?
We can approach 2 ways to achieve this?
1) Sales - Above(Sales) // Front end
2) Sales - Previous(Sales) // Script
What exactly is a here?
[prep average]:
LOAD $(a)-1 as a_avg,Client as Cl_avg,sum(if(Year=$(a)-1,Sales,0)) as Sales_a_1
Resident [Sales]
Group by a_avg,Cl_avg
I tried to do it with the "previous" function but it doesn't work since that function loads the previous loaded value. It computs then the average between corporate and retail which is not the purpose.
a is the index in the for loop
Makes sense, not sure how I missed that... would you be able to share 4-5 rows of data to check this out?
May be provide sample data set and result set that may demon the issue
Sorry for the late reply.
So here is the exact script:
[Sales]:
LOAD * inline [
Year,Client,Sales
2010,Corporate,1000
2010,Retail,250
2011,Corporate,1050
2011,Retail,300
2012,Corporate,1025
2012,Retail,305
2013,Corporate,1047
2013,Retail,312
2014,Corporate,1068
2014,Retail,400
2015,Corporate,1071
2015,Retail,403
2016,Corporate,1083
2016,Retail,407
];
for each a in FieldValueList('Year')
[prep average]:
LOAD $(a)-1 as a_avg,Client as Cl_avg,sum(if(Year=$(a)-1,Sales,0)) as Sales_a_1
Resident [Sales]
Group by $(a)-1,Client;
JOIN([prep average])
LOAD $(a) as a_avg,Client as Cl_avg ,sum(if(Year=$(a),Sales,0)) as Sales_a
Resident [Sales]
Group by $(a),Client;
next a
[average]:
LOAD a_avg,Cl_avg,(Sales_a_1+Sales_a)/2 as average
Resident [prep average];
This is how the table [prep average] looks like
So that I get nothing on the table [average]
Are you looking to get this?
Try this
[Sales]:
LOAD * inline [
Year,Client,Sales
2010,Corporate,1000
2010,Retail,250
2011,Corporate,1050
2011,Retail,300
2012,Corporate,1025
2012,Retail,305
2013,Corporate,1047
2013,Retail,312
2014,Corporate,1068
2014,Retail,400
2015,Corporate,1071
2015,Retail,403
2016,Corporate,1083
2016,Retail,407
];
FinalSales:
LOAD *,
If(Client = Previous(Client), RangeAvg(Sales, Previous(Sales))) as AvgSales
Resident Sales
Order By Client, Year;
DROP Table Sales;
Sorry for the late response.
Thank you so much it really helped me !