5 Replies Latest reply: Jun 11, 2013 9:06 AM by Sandro Krumbein RSS

    Showing gaps in data in the graph

    Sandro Krumbein



      I have a problem regarding the visualization of sales data.


      We have ArticleNo, Date, Quantity. Not all articles sell on all dates and thus won't have an entry in that table. And we have a MasterCalendar, which contains all dates, including those where nothing is sold.


      I'd now like to create a bar chart along the date and article dimensions, which shows sales per date (sum(Quantity)). But I'd like to leave a gap where no sales of the article was made. At the same time I want to honour the time selections, e.g. sales for April. for that reason the "show all values" of the date dimension doesn't seem to be the solution.


      I have tried all conceivable combinations of "Show all values", "Ignore null values" (for dimensions) and "Ignore null values" and "Ignore missing values" (for the presentation). I have also tried various combinations of set analysis including the attempt to create a dynamic dimension, which specifically included all dates that I want and no more

      e.g. aggr(only({1<Year=$::Year,Month=$::Month,Day=$::Day>} Date), Date)

      by which I then hoped to be able to use the "Show all values"option


      But all to no avail. I am about to conclude that I have to pump up the sales data with zeros for all article-date-combinations, which don't contain sales. Is what we need not possible or am I missing something here?


      Thank you very much!