# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
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.

1 Solution

Accepted Solutions
MVP

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

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

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

Contributor
Author

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.

Contributor
Author

a is the index in the for loop

MVP

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

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)
Contributor
Author

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

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;

Contributor
Author

Sorry for the late response.

Thank you so much it really helped me !

Community Browser