# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
New Contributor

## Average sales between 2 years

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]:

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.

Tags (3)
1 Solution

Accepted Solutions
MVP

## Re: Average sales between 2 years

Are you looking to get this?

Try this

[Sales]:

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:

If(Client = Previous(Client), RangeAvg(Sales, Previous(Sales))) as AvgSales

Resident Sales

Order By Client, Year;

DROP Table Sales;

9 Replies

## Re: Average sales between 2 years

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MVP

## Re: Average sales between 2 years

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

New Contributor

## Re: Average sales between 2 years

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.

New Contributor

## Re: Average sales between 2 years

a is the index in the for loop

MVP

## Re: Average sales between 2 years

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
New Contributor

## Re: Average sales between 2 years

So here is the exact script:

[Sales]:

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]:

Resident [prep average];

This is how the table [prep average] looks like

So that I get nothing on the table [average]

MVP

## Re: Average sales between 2 years

Are you looking to get this?

Try this

[Sales]:

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:

If(Client = Previous(Client), RangeAvg(Sales, Previous(Sales))) as AvgSales

Resident Sales

Order By Client, Year;

DROP Table Sales;

New Contributor

## Re: Average sales between 2 years

Sorry for the late response.

Thank you so much it really helped me !