topic Re: How to sum(qty) within variable time period in QlikView

How to sum(qty) within variable time period

Fri, 04 Jan 2013 17:57:34 GMT

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

Fri, 04 Jan 2013 18:45:03 GMT

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 — Fri, 04 Jan 2013 20:10:05 GMT

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

Wed, 16 Jan 2013 17:44:42 GMT

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

Mon, 28 Jan 2013 14:21:38 GMT

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