topic Re: Average sales between 2 years in QlikView

Average sales between 2 years

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

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

Re: Average sales between 2 years

Anil_Babu_Samineni — Mon, 21 Aug 2017 10:00:54 GMT

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

Re: Average sales between 2 years

sunny_talwar — Mon, 21 Aug 2017 11:58:19 GMT

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

Re: Average sales between 2 years

Anonymous — Tue, 22 Aug 2017 07:48:56 GMT

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.

Re: Average sales between 2 years

Anonymous — Tue, 22 Aug 2017 07:49:40 GMT

a is the index in the for loop

Re: Average sales between 2 years

sunny_talwar — Tue, 22 Aug 2017 15:31:49 GMT

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

Re: Average sales between 2 years

Anil_Babu_Samineni — Wed, 23 Aug 2017 04:14:04 GMT

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

Re: Average sales between 2 years

Anonymous — Wed, 30 Aug 2017 10:32:30 GMT

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]

Re: Average sales between 2 years

sunny_talwar — Wed, 30 Aug 2017 16:37:03 GMT

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;

Re: Average sales between 2 years

Anonymous — Sun, 24 Sep 2017 17:55:11 GMT

Sorry for the late response.

Thank you so much it really helped me !