Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

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

1 Solution

Accepted Solutions

Re: Average sales between 2 years

Are you looking to get this?

Capture.PNG

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;

View solution in original post

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)

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

mafattma
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.

mafattma
New Contributor

Re: Average sales between 2 years

a is the index in the for loop

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)
mafattma
New Contributor

Re: Average sales between 2 years

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

prep avg.PNG

So that I get nothing on the table [average]

average.PNG

Re: Average sales between 2 years

Are you looking to get this?

Capture.PNG

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;

View solution in original post

mafattma
New Contributor

Re: Average sales between 2 years

Sorry for the late response.

Thank you so much it really helped me !