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 email@example.com. Thank you! -Rich
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.
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
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,
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,
Buffer Size Solutions.qvw 179.5 K