3 Replies Latest reply on Dec 9, 2016 8:28 AM by Shinichiro Murakami

    Fixed LOD across multiple data sources

    Chad Klein

      Hello Tableau Community!

       

      So I'm trying to do some metrics on some of our company assets and I have a question regarding LOD calcs across multiple data sources.  I know that the canned LOD calcs can't be used across multiple data sources, but there must be a way to accomplish what I'm attempting and and have not been able to find it.

       

      In my workbook I have 3 data sources: Hours, Working Hours, and Idle Hours

       

      What I'd like to calculate is a percentage of true idle hours, so the basic idea would be (Idle Hours - Working Hours )/Hours

       

      I created a calculation for True Idle with Idle as my primary data source that is the following:

       

      True Idle Hours = IFNULL(SUM([idle_hours]) - SUM([Working Hours].[pto_hours]), SUM([idle_hours]))

       

      Then I created a calculation that is Percent True Idle that is the following:

       

      Percent Idle ([True Idle Hours])/SUM([Hours].[hours])

       

      This of course doesn't work when I try to create a line graph of avg Percent Idle over time because the level of detail is wrong.  What I would normally do is create this calculation:

       

      {FIXED [asset] : SUM(True Idle Hours)/SUM(Hours)}

       

      But I can't do that because Tableau can't do LODs across multiple data sources.  I'd love to share a sample workbook, but all of the data is confidential, so I'm not able to.  Anyone have any suggestions of a fix or some literature that might point me in the right direction?