4 Replies Latest reply on Nov 18, 2015 7:46 AM by Joshua Milligan

    Calendar Data Overlay


      I've produced a school calendar that displays information.  One of the dates comes up blank. This occurs when there's no data entry matching that day.  That would be fine except my day of the month number doesn't show up either.  (Shown below on August 24th.)


      My calendar populates from an Excel file of calendar dates.  The overlay data entries come from a read-only SQL database.  If both data sources were in SQL, I'd create a union on the custom query.  (I do not want to create any link server entries.)  Mixed data sources give quite a few possibilities for other overlays.


      In the example file, I've created two locations via a PickALocation parameter to simulate multiple locations in my data.  The data to display would be a count of entries (exemplified as a calculated field, c.MyDataValue).


      How can I get the date number to show on the 24th when there's no data associated to the selected location?

        • 1. Re: Calendar Data Overlay
          Alexander Mou

          Embed the parameter in the count and it works.



          On Thu, Oct 29, 2015 at 8:37 AM, james.n <

          • 2. Re: Calendar Data Overlay

            Looks like what I need.  Can you post the file you published so I can see how you did it?



            (I figured out why the date was missing in my version.  My drop down location has over 70 entries.  When the drop down affects filtering of the data, it also filters the calendar numbers.  I created a base table with a baseline for each location and it works.  But that requires 25,000 entries per calendar year on the spreadsheet.)

            • 3. Re: Calendar Data Overlay
              Alexander Mou

              Follow the link above to download


              On Friday, October 30, 2015, james.n <tableaucommunity@tableausoftware.com>

              • 4. Re: Calendar Data Overlay
                Joshua Milligan

                Here's another possibility, don't use the Location Id from the secondary source as a filter.  Instead, create a calculated field in the primary source based on the parameter and use it as part of the blending.


                You'll notice here, that I've removed the filter and kept the measure as it was, but all the counts are correct based on the parameter selection:



                I accomplished this by creating a calculated field in the primary source that just contained the parameter value:



                And then include it on the blend in the secondary source:



                The reason this works, is that often, when you use a filter from a field in the secondary source -- the filter is applied after the blend. So, if there wasn't a Location Id record for that date, the date record gets removed.  However, if we don't use the filter and instead use the blend, then we get all fields from the primary source (every date) and any corresponding fields from the secondary.  The Location Id part of the blend limits it to be data only for the desired location.


                I've attached a workbook to demonstrate how it would work.  Hope that helps!


                Best Regards,