1 Reply Latest reply on Jul 11, 2018 8:21 PM by swaroop.gantela

    Blended data sources with differing granularity; calculated fields.

    Marcus Bright

      Hi Tableau Community,

       

      I've ran into a bit of road block with regards to a quarterly report that I've previously created.

       

      In the main data source (D1) we have fields such as Financial Year, Quartely, Month as well as the organisations submitting the data.

       

      I now have a need to blend this to bring in plan figures which is coming from a separate reference table (D2); unfortunately this is only quarterly figures however so i end up matching on everything bar the month field.

       

      The calculated field which i'm having trouble with is one where i need to calculate the number of days. There is no date field, just financial year then Q1 for the quarters...even month is just listed as '1,2,3' etc.

       

      Here is the calculation as it stands; 

       

      (SUM([Measure Value]) * SUM([DayCount (Reporting) (2)].[Day Count])) * 90 / 100

       

      This gives me an overall figure, but this was only achieved because i used only the D2 data source with another 'DayCount' reference table as a test.

       

      The primary data source needs to be D1, so that i'm able to filter the calculation when it comes to adding the relevant sheet to it's intended dashboard.

       

      Unlike the above the day count reference table is not an option as it just ends up counting all instances of 'Q1' from the primary data source and gives me over the top figures. What i need is a way for SUM([DayCount (Reporting) (2)].[Day Count])) to update based on the primary data sources quarter filter...

       

      Q1 = 91

      Q2 = 92

      Q3 = 92

      Q4 = 90

       

      I've tried to group this as a calculated field via D1 & D2 but again i end up with over the top figures due to the main data set being quite large with thousands of instances of Q1 etc.

       

      The underlying data is confidential so i wouldn't be able to share the report, any help would be appreciated however...i know it's a messy one.

       

      Happy to answer any questions or provide extra details.

       

      Kind Regards,

       

      Marcus

        • 1. Re: Blended data sources with differing granularity; calculated fields.
          swaroop.gantela

          Marcus,

           

          I think the number of days per quarter can be all done from fields in D1.

           

          I first converted the month into a proper date:

          MAKEDATE([Year],[Month],1)

           

          Then used window calculations to get the first and last months of the quarter:

          WINDOW_MIN(MIN([Convert to Date]))

           

          likewise: WINDOW_MAX(MAX([Convert to Date]))

           

          Then the number of days per quarter can look something like this:

          DATEDIFF('day', [MinMonthQuarter],

               DATEADD('day',-1,DATEADD('month',1,[MaxMonthQuarter]) ) ) +1

          // difference between first day of quarter and one day before beginning of next month)

           

          Please see workbook attached in the Forum Thread.

           

          275299blend.png