4 Replies Latest reply: Jan 28, 2013 9:22 AM by Rich Tefft RSS

    How to sum(qty) within variable time period

      I have have a simple table containing an Order_Date, Order_No, and Qty.  I need to determine the highest sum(qty) for a variable date period.  For example, "What is the highest total qty ordered within any 3 day period?".  The number of days will be a variable.

       

      Struggling a lot with set analysis (still a few weeks until formal QV training) so any help would be appreciated.  Example QVW is attached.

      Thanks in advance,

      Rich

        • Re: How to sum(qty) within variable time period

          The forum is having problems at the moment. I received email from the forum that a reply was posted, but no reply appears for the post, and the email link in the notification was invalid.  If you post a response and do not see it here, please email it to me at richard.tefft@scipax.com.  Thank you!  -Rich

          • Re: How to sum(qty) within variable time period
            Gysbert Wassenaar

            See attached example. In the input box you can enter the length of the period. In the table below the highest value is highlighted. Scroll down to 04/02/2012 to find it. I haven't found a way to calculate only that value and display it in a text box.

             

            edit: I've added another example qvw with a bar chart that shows the value of the maximum period sum in the chart. So you can see it at a glance instead of having to scroll down a straight chart.

              • Re: How to sum(qty) within variable time period

                Thanks for the info.  When you updated my Rolling Window Example I noticed you added filler records for all the days with no orders.  This would easily be millions of extra rows in our case and just isn't feasible to do for all the order data in our ETL.

                 

                Looking at the Sales To Date file, the sum works until I add an additional list box to filter on SalesPerson.  The filter causes gaps in the dates and the expression returns incorrect results. The expression is working on X number of records below, where we need X number of days.  Our data will have gaps and the solution we need has to handle that without adding filler records in the ETL.

                 

                One idea we have is to keep an isolated master calendar with 1 record for every date, and a Qty=0.  When the user makes their selection on the sales data, we would somehow union the calendar records with the selected data, and get a sum(qty) grouped by date.  This would give us 1 record per date and allow the BELOW function to work for us.  I'm from a SQL background so while I can express this in SQL terms, I can't get it in QV.

                 

                Pseudo-SQL would be something like this.  It acts like a dynamic filler (this keeping size manageable):

                 

                select date, sum(qty)

                from (select user's date, user's qty from our data

                         union

                         select date, qty 0 from our calendar)

                group by date;

                 

                If we could generate that, I believe we could adapt your expression to it since 1 row = 1 day.  Any ideas?

                Thanks for the help,

                Rich

                  • Re: How to sum(qty) within variable time period

                    We have found 2 different solutions.  Gysbert's idea to foce all records to appear was the key, creating an outer-join condition.  Thanks to instructor Mike Alegado, we now have a variable-based expression to calculate the window for each dimension value, and an AGGR expression to capture the highest value.

                     

                    The second method is sneakier.  By putting an automatic Max  calculation on the exprsssion column in the chart, Qlikview can automatically calculate the max for us.  We then capture that value directly from the chart cell using VBScript (thanks forum!).

                     

                    I've attached a sample file with the adjustable date range and both solutions implemented.

                    Thanks to all for the help,

                    Rich