4 Replies Latest reply: Aug 16, 2012 6:04 PM by Stefan Wühl RSS

    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?

        • Re: Average Annual Demand
          Stefan Wühl

          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

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

                • Re: Average Annual Demand
                  Jason Michaelides

                  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

                  • Re: Average Annual Demand
                    Stefan Wühl

                    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.