3 Replies Latest reply on Mar 29, 2016 4:58 AM by Li Kim Lee

    Filter on joined data sources omits dates if no filter values exist (despite no relation to data plotted)

    Li Kim Lee

      Some help with the following would be much appreciated. I have tried "show missing values" and the solution on Unable to Show Missing Values (Dates) when date level is switchable using parameter but both approaches do not work for my data sources.


      I have two data sets, one with daily values ("Time Period" dimension) and one with values for Mondays only ("Week Commencing" dimension). I would like to plot values from both data sets on a week-by-week basis so I have used DATETRUNC to change the daily values to weekly dates. A "End Date Selector" parameter filters the data to show data ending on a specific "week commencing" date. The data is also filtered to dimension values in the daily data set (i.e. "Colour").


      Problem: A dimension value (i.e. Colour = Blue) in the daily data set is not present for an entire week (i.e. 29 Feb 2016 - 6 Mar 2016). When the End Date Selector is set to 29 Feb 2016 and the Colour filter to only Blue, then the graph shows the last week for which data exists, which is 22 Feb 2016. However, the data in the graph is from the other weekly data set and has no actual relationship to the Colour dimension in the other daily data set. The issue above goes away if I select subsequent weeks (e.g. 7 Mar) for the End Date Selector and the Week Commencing pill is Continuous and Shows Missing Values... which is not the intended solution as many instances exist where the End Date Selector will be for a week where no values for Colour exists (e.g. Colour = Red, End Date Selector = 21 Mar).


      How can I get the graph to show the data up until the date selected in the End Date Selector even when filtered to values that do not exist in a different data source (which are not related to the existing primary data source)? The workbook is attached. Thanks in advance!