We often need to create a line chart by date, and to have the line go to 0 when there is no data for that date. When tracking sales for instance, if you have sales on January 10 and January 12, but none on January 11, you do NOT want the line to go directly across the screen from January 10 to January 12. You want it to go down to 0 on January 11 to represent the fact that there were no sales on January 11.
I suspect that most people solve this problem by using a date island. The first chart in the attached file demonstrates that approach. However, the poor performance of this approach is often unacceptable.
The second chart solves the performance problem by using a continuous axis with a static min and max. As long as you have a January 11 date in the system (such as from a calendar table), the line will go to 0 on that date. However, I then lose control over the formatting. For example, I have cases where I want to split the day into three "Turns" (labeled A, B and C) of 8 hours each. The continuous axis will of course not display the turns. Or I may want to group dates by week. The continuous axis will continue to display legends for days that may or may not fall on my week boundaries. These display problems have caused a user complaint.
The third chart represents another failed attempt to get this to work. I used "Show All Values" to force the January 11 date to display. It does in fact display with the line going to 0 properly. However, the chart of course shows ALL dates, not just the range I've selected.
I need to come up with a solution to this problem that behaves properly (goes to 0, correct legends) and performs well. So far I have had no luck.
Has anyone else solved this problem in a high-performance way?
Note that I'm currently on version 9 SR6. If there's a version-10-only solution, I'd like to hear about it because we'll get there eventually. But if possible I'd like to solve it before then. Thanks!
Edit: Minor bug fix. Min and max on continuous axis were ignoring the DateTurn field because it is also on the RealData table. Had to exclude it from the concat explicitly, and thus INCLUDE it in the fields that the min and max are sensitive to.
Edit 2: I just tried bringing it up in version 10 instead of 9, and charts 2 and 3 aren't displaying lines, only points. That's bad. Chart 3 also seems to be ignoring "Show All Values" and is ONLY showing me points with data. That would also make it useless as a solution. These problems may also prevent most of you from being able to help, though if you can get it working in 10, hopefully it will also work in 9.
Edit 3: Changed attached file to add a Customer field and expand on the text explanation. Customer 'Carla' made all the purchases in the selected date range, so selecting her shouldn't affect the charts at all. It doesn't for the example charts, but this feature must be preserved in any solution.
I stumbled onto this thread while searching for the same solution. Just wondering if any "cleaner" solution was ever found for this? we're still on qv9 but i hear we're getting v11 later this year so an 11-specific solution would be helpful also.
I'm not aware of any cleaner robust solution than that demonstrated by testShowMissing10 above, but I've not been active on the forum recently, so could have easily missed a posted better solution.
