Average sales between 2 years

[Anonymous]

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

[Anil_Babu_Samineni]

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

[sunny_talwar]

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

[Anonymous]

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.

[Anonymous]

a is the index in the for loop

[sunny_talwar]

Makes sense, not sure how I missed that... would you be able to share 4-5 rows of data to check this out?

[Anil_Babu_Samineni]

May be provide sample data set and result set that may demon the issue

[Anonymous]

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]

[sunny_talwar · Accepted solution]

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;

[Anonymous]

Sorry for the late response.

Thank you so much it really helped me !