1 Reply Latest reply on Jan 30, 2012 12:59 AM by Richard Leeke

    Sum of values across two sources not working with filter

    Andrew Fisher

      Hi all,


      I'm not sure why this isn't working but here's what I'm doing.


      I have two data sources that otherwise work fine using data blending. The relationship between the two sources is using date fields which I've created custom relationships for on all the relevant levels of date.


      Each data source has rows based on days and when I operate at the level of month, day, quarter or year this works absolutely fine.


      In source 1 I have orders and in source 2 I have targets. Orders in this context is primary, targets are secondary


      What I want to have is a cross tab that drills down to only the level of a month (ie Year, Q, Month) and then display values across a filtered period (eg a 2 week window).


      When I do this the total of my orders works absolutely as expected however my total for my targets values are the sum of the **entire** month not the filtered range.


      Is there a way to ensure that a filtered range applies across all data sources linked into a worksheet?




        • 1. Re: Sum of values across two sources not working with filter
          Richard Leeke

          I've attached a workbook which tries to demonstrate what is happening and how you may be able to achieve what you want - but it also illustrates another trap you can hit with data blending.


          The workbook blends the Coffee Sales and Superstore Sales data sources, just showing annual sales from the two data sources.


          The first sheet shows the problem you are having.  Coffee sales (primary) are filtered by date, and correctly reduce and increase as you slide the filter around, including more or less of the sales for the year. Superstore sales, however, show the full sales for the year as long as there is anything at all for the year included in the primary datasource.  This is because the blend is happening at the level of detail that is displayed for the primary datasource, which is YEAR().


          In the second sheet I have added QUARTER([Date]) and MONTH([Date]) to level of detail.  I've also put MONTH() on colour, to show clearly what is happening.  Now the secondary data source sales also respond to the date filter, with months coming and going from the bars as you slide the filter around. But watch carefully.With the start of the filter set to mid June 2010, no sales at all are shown for June for either data source.  That is because Coffee Sales data is monthly, generally reported on 1st or 2nd of the month.  So filtering from 19th June doesn't pick up anything for June.  As there are no primary sales for June, there's nothing to blend against, so there are no Superstore sales shown - even though Superstore sales are reported throughout the month (see the last sheet).


          The third sheet just shows the same values as the second, but using a table calculation to aggregate across months, so that there is a single bar for the total of the filtered values from Level of Detail.  as with the second sheet, the filter on the Primary data source can exclude secondary data that actually falls within the filtered range.


          So yes, you can do what you want - but handle with care and think hard about how your data is structured.  If you have just the odd day missing from your primary data source, the fact that those days are not included from the secondary would be very easy to miss.