Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

Anonymous
Not applicable
Author

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_Michaelides
Luminary Alumni
Luminary Alumni

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

swuehl
MVP
MVP

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.