Showing all days within selection, even if no data / ValueLoop?

[#1]

I'm trying to create a table that shows all orders within the selected period (say, December 2009). We don't have orders on every day, but our customer would like to see every day (including weekends). I've got a Calendar table and an Orders table, which are linked with the date as a %DateKey. My graphs dimension is %DateKey, the expression is Sum(OrderCounter), where OrderCounter is 1 for each order. This creates a simple graph with an orders-per-day view.

I first tried to enable "Show All Values". This shows all days, including weekends without orders. Of course, if I select December 2009 it also shows all days before and after December 2009, which makes the graph unreadable. How can I limit the graph to only show December 2009 but still have "Show All Values" enabled? I've thought of generating a "fake" order for every day on which we don't have orders, but that would fail if the user selects a specific kind of order. The only option then would be to generate a fake order for every possible type of order we have (thousands!) for every day that those orders haven't been placed (almost every day in 4 years of data for most order types). We're talking millions of fake records just for the sake of a displaying all days in a graph.

I also came across the ValueLoop function, which appears to be perfect: throw in the minimum and maximum selected date (December 1st - December 31st) and it should generate 31 days for me. For some reason ValueLoop does not change when the selection changes. Does anyone know why, or what I should do to make ValueLoop use the current selection?

[johnw]

A common solution to this kind of problem is a date island. You create a fake date field separate from your main data that has every possible date. In your chart, you use the fake date as the dimension, then connect it to the real date with an expression like sum(if("RealDate"="FakeDate",OrderCounter)). Unfortunately, performance can be pretty bad on large data sets, and you can get strange results when both the real date and the fake date are selectable.

I honestly haven't come up with a better general solution, though. Date trending with zeros just seems to be a weakness of the product.

[#3]

Your solution makes more sense to me.

But somehow, even if I select a certain date range from my actual data, the fake dates prior to my selected range are still shown in my graph and I did check and uncheck all possible "show all/ missing/zero values" options etc.

[#4]

Strangely enough, fake dates AFTER my selected dates do not show up in the graph.

[johnw]

As I said, "you can get strange results when both the real date and fake date are selectable". You're seeing one of those strange results. When you select the REAL date, it only restricts what data you're matching up to the fake date. The fake date chart can't tell the difference between data that doesn't exist (for which it should show 0) and data that exists but you wanted to exclude via your selections. So it goes ahead and shows zeros.

It's a pain, and there may be a better approach, but I believe this keeps your two calendars in sync (combined with onSelect and onChange triggers on the appropriate fields):

sub setDate()
ActiveDocument.getField("FakeDate").selectValues ActiveDocument.getField("Date").getSelectedValues(10000)
end sub
sub setMonth()
ActiveDocument.getField("FakeMonth").selectValues ActiveDocument.getField("FakeMonth").getSelectedValues
end sub
sub setYear()
ActiveDocument.getField("FakeYear").selectValues ActiveDocument.getField("Year").getSelectedValues
end sub
sub setFakeDate()
ActiveDocument.getField("Date").selectValues ActiveDocument.getField("FakeDate").getSelectedValues(10000)
end sub
sub setFakeMonth()
ActiveDocument.getField("Month").selectValues ActiveDocument.getField("FakeMonth").getSelectedValues
end sub
sub setFakeYear()
ActiveDocument.getField("Year").selectValues ActiveDocument.getField("FakeYear").getSelectedValues
end sub

So if you select a Month from your actual calendar, the macro selects the same Month from your fake calendar. So even if you had only three days of actual data, you'll end up showing all of the days of the month in your chart. Similarly, if you select a month in your chart, it will select your actual month. So I think it would fix the behavioral problems, just not any performance problems.