1 Reply Latest reply on May 8, 2018 10:37 AM by Deepak Rai

    Trouble Adding a FIXED LOD data element with a non-fixed element in the same data source (SFDC/Tableau Connector)

    Brett Bernsteen

      Hi there.

      I work on a professional services team and I'm trying to sync up Salesforce with Tableau. It's been smooth sailing so far, but I'm having trouble re-creating all the calculated fields in Salesforce that are populated by a Harvest integration (a time tracking tool).

      Each client project is stored in our Salesforce Project Object. Those projects tie back to the Account object (one to one relationship), and a "Time Entries" object (a one to many relationship), which creates a unique record for each instance of time tracked against that project.

      Since introducing the Time Entries project to my join, I've had some difficulty with Measure values being multiplied by the number of Time Entry records for a project. For Instance, if I have a Project object Measure value for "Total Project Fees" of 1,000 and there are 250 time entries against that project, Tableau is taking (1,000 x 250) and displaying that for the project's Total Project Fees.

      Most of these issues can be easily shored up using a "Fixed" LOD equation, where:

      {FIXED [Project Name]: MIN ( [Total Project Fees] ) }

      However, I'm running into issues when trying to sum together a fixed LOD value with a non-fixed LOD value. It completely ignores the FIXED operator and multiplies the value by the number of records.

      For instance - I'm trying to derive a "Total Client Cost" for a client based on the duration of all time tracked against their project (which comes from the Time Entries object and has multiple records that accumulate to create a total duration), the legacy client relations time, and the legacy development time (which comes from the project object).

      I've successfully de-duplicated the Legacy Dev time and Legacy CR time using the FIXED formula, but I don't want to use FIXED for duration, because those instances are unique for each time entry. When I try to add together [Duration] + [Legacy CR Time] + [Legacy Dev Time], my calculation is not correct.

      Here's an example, and a more complete data set can be found in the image link.

      Project: "utc: Launch" --> stored in Project Object

      Duration: 49.02 --> stored in Time Entries object across 96 unique time entry records; did NOT use a FIXED calculation since I need it to add up each time entry

      Legacy CR Time: 0 (used a FIXED calculation)

      Legacy Dev Time: 32.55 (used a FIXED calculation)

      One would think the Total Client Cost would be 32.55 + 49.02 + 0 = 81.57, but that's not what Tableau is giving me. It's giving me 49.02 + (96 * (32.55 + 0) = 3173.82

      I realize this is messy, but any ideas?