Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
bzim8481
New Contributor

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
MVP
MVP

Re: Average Annual Demand

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

INVOICES:

LOAD * INLINE [

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,

Stefan

bzim8481
New Contributor

Re: Average Annual Demand

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.

jason_michaelid
Honored Contributor II

Re: Average Annual Demand

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,

Jason

MVP
MVP

Re: Average Annual Demand

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.

Community Browser