1 Reply Latest reply on Aug 22, 2016 12:35 PM by Yuriy Fal

    Calculate Holidays by using tableau server and excel data blending

    himhim chan

      I want to calculate the holidays in a certain period but I cannot find suitable solution after reading many articles from: FAQ: Holidays & Workdays 

       

      I am using Tableau 9.3.

      Primary datasource: Event detail (Tableau Server)

      Secondary datasource: Holidays (Excel)

       

      For example,

       

      Event detail, Tableau Server:

      Eventstart dateend date
      a5/10/201622/10/2016

       

      Holidays, Excel:

      dateholiday
      8/10/2016x
      16/10/2016y

       

      I would like to have the result:

         

      Eventstart dateend dateCount holidays
      a5/10/201622/10/20162

       

      I tried the formula below, but it show me nothing.

      IF min([General Holiday (General Holiday 2014-2017)].[Date]) >= min([Order Date])

          AND

          min([General Holiday (General Holiday 2014-2017)].[Date]) <= max([Order Date])

          THEN SUM([General Holiday (General Holiday 2014-2017)].[Number of Records])

          END

      擷取.PNG

        • 1. Re: Calculate Holidays by using tableau server and excel data blending
          Yuriy Fal

          Hi himhim,

           

          What you're trying to achieve is not possible with data blending.

          It is mainly because of your event datasource looks like an accumulative snapshot

          (each row is a unique event id, and it has several "status" date fields, such as start / end date).

           

          But for the question you've asked another data structure is best suited, namely a periodic snapshot.

          There would be several rows for each unique event id -- as many as days from start to end date --

          and each row has a unique event id / date combination. Then one could blend holidays on a date field.

           

          Having datasources like yours, a couple suggestions could be made:

           

          1) If possible, put your holidays table into the same database scheme

          (of the underlying DBMS instance) as where your event table resides.

          Then do a non-equi join on dates, and prepare an extract from that.

           

          2) If you're able to read-only from an underlying DBMS,

          then you could use Custom SQL (a UNION ALL Select)

          to Pivot the event table -- by aligning both original fields

          (Start Date and End Date) into a pair of new fields

          (say, Pivot field names and Pivot field values).

           

          Having a datasource like this, you'd be able to blend

          your Holidays (Excel) datasource on a Date field.

           

          Please look at the attached as an example of the latter.

          Hope it could help.

           

          Yours,

          Yuri

           

          PS Tableau 10 Cross-Datasource Joins couldn't help with the 1st approach,

          because of non-equi joins not possible across DS, hence 2nd approach only --

          but with a 'regular' left join the subsequent calculations could be built

          a bit simpler than with a 'conventional' blend.

          2 of 2 people found this helpful