7 Replies Latest reply on Jul 16, 2018 6:41 PM by Hari Ankem

    Cross database calculation

    Barata Ribs

      Hello everyone, not sure if this is a very simples task and I´m missing something, but here we go:

       

      I have two datasets, both with a common measure and a date/time tag with different granularity. The first is a large dataset (coming from my SQL database), with a date/time tag every 10 minutes, while the second is a small dataset with only 12 values (one for each month, assigned as 1 to 12).

      The small dataset predicts an expected value (Measure 2) for the monthly sum of the large dataset (Measure 1).

       

      I am trying to calculate the difference between both. I managed to plot both in the same chart using "Data Relationships" but I wasn't successful using Table calculation or Calculated field.

      This is how the Calculated Field appears to me when I try to add my measures:

       

      [Masure 1] - ( [Datasource (Name of Connection)] . [Measure 2])

       

      I imagined Table calculation would understand what I want and it would be straightforward, but no luck. I tried the Calculated field thinking that I could aggregate measure 1 by month (1 to 12) but couldn´t do it.

      Any thoughts?

       

      To help in understanding the measures, one could think the measure 1 as an energy meter registering energy consumption every 10 minutes, and the measure 2 as the expected monthly energy consumption for a given year.

       

      Thank you.

        • 1. Re: Cross database calculation
          Hari Ankem

          Can you attach your packaged workbook?

          • 2. Re: Cross database calculation
            Peter Fakan

            Hi Barata,

             

            Firstly, quickly unpacking the details you have posted - [Masure 1] - ( [Datasource (Name of Connection)] . [Measure 2])

             

            This can be summarised as [Measure 1] - [Measure 2] the rest of the information is just a bit of code that Tableau inserts to tell itself that you mean the same-named value from the second data source instead of the first, and the brackets around the 2nd value aren't doing anything mathematical that I'm aware of.

             

            This is great because unless you have a glaring red error message at the bottom of your calculated field, this means you have gotten through the edit relationships menu and have a blend instead of a join - all important stuff

             

            Lets look at the issue again - you have a date field aggregated to minutes on one side, and a date field aggregated to months on the other, but you have the blend working (assumption). The solution I believe you are looking for is to aggregate your minutes side up to months. This might be as simple as converting it to discrete and choosing the correct aggregation level (months), or extracting a datepart (DATEPART('month',[date]), or something yet to be identified, but as Hari noted above, we need to see an example to ascertain which way to go.

             

            HTH

             

            Peter

            • 3. Re: Cross database calculation
              Barata Ribs

              Hello Hari Ankem, thanks for reaching out!

              I prepared a workbook to simulate what I was doing, I resampled the timesteps to 1 hour just to make it easier.

               

              Check it in the original post!

              • 4. Re: Cross database calculation
                Barata Ribs

                Thanks Peter, find the workbook attached in the original post.

                 

                Any way to aggregate in the Field Calculation?

                I know how to aggregate the time series in the chart (as a pill), but I´m having trouble associating both time series in the calculation by the common timestep (month) .

                • 5. Re: Cross database calculation
                  Hari Ankem

                  I have joined your data like this

                  1.png

                   

                  and using the following calculations

                   

                  • Expected Value: AVG({FIXED [Month],[Timestamp]:AVG([Measure 2])})
                  • Actual Value: SUM({FIXED [Month],[Timestamp]:AVG([Measure 1])})
                  • Difference: [Actual Value]-[Expected Value]

                   

                  and made this output

                  1.png

                   

                  OR

                   

                  You can blend your data too and get the desired output.

                  1.png

                   

                  The updated workbook is attached. Hope this helps.

                  1 of 1 people found this helpful
                  • 6. Re: Cross database calculation
                    Barata Ribs

                    Wow! Thanks a lot Hari, it definitely helped!

                    I was missing how to calculate it, I guess the trick is the "FIXED" function, I´ll search more about it but I´m pretty sure it solves my problem.

                    • 7. Re: Cross database calculation
                      Hari Ankem

                      You are welcome. FIXED is one among a few LOD statements. You should explore them. They are very useful.