Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
Showing results for 
Search instead for 
Did you mean: 

Average Annual Demand

I need to try and find the average annual demand for products based off of a history of invoices. I have not had a lot of success with incorporating the 'Avg' command or figuring out how to aggregate for the entire year within the script. I need this value to use in another formula, so I would like to have it outputted from the script alone. Any ideas?

4 Replies

If you want to calculate your average annual demand in the script, you probably need to look into the aggregation functions in combination with a group by clause.

You could the sum up all invoice amounts per product and year, then average the sums per product, maybe like



Product, InvoiceDate, Amount

A, 01.01.2011, 10

A, 01.06.2011, 20

A, 01.02.2012, 15

A, 01.06.2012, 12

B, 01.06.2010, 10

B, 01.02.2011, 25

B, 01.08.2011, 5

B, 01.05.2012, 12

B, 01.06.2012, 21


LOAD Product,

avg(Sum) as AverageAmount

group by Product;

LOAD Product,

year(InvoiceDate) as Year,

sum(Amount) as Sum

Resident INVOICES group by Product, year(InvoiceDate);

Hope this helps,



That is a good idea and thank you for your response. However, I am dealing with a JDE data base and have over 300K entries over the past 2 years alone so inputing it manually in an Inline Table is a little daunting to say the least. If there is a was to sum values ove the entire year within the load or SQL script that would be my best bet.


I suspect Stefan was just using an inline table to generate some example data to illustrate a potential method 🙂  If you are not sure how to load data into QlikView from an ODBC source then you would do best to search for the "build your first QlikView application" on the qlik.com website and get familiar with the basics before venturing further.

Hope this helps,



Right, the INLINE LOAD is just used to create some data to play with, I like script snippets that can just be copied into QV and executed. In your setting, you can remove the INLINE table completely, instead load your table.

You probably need to adjust some field names etc. Above should just show you a possible way of doing what you requested. The important / relevant part is the lower one, using group by clauses and aggregation functions.