4 Replies Latest reply on Apr 18, 2013 2:27 PM by Peter Thomsen

    Calculation from SQL query

    Kim Rees

      Is there a way to create a calculation from a SQL query? For instance, if I want to have a value that is the mean population from 1970-1975. This is a number I want to use as a baseline for one axis. So, for instance my axis might be % change of population from the baseline. The baseline would be calculated and the % change would be a calculation against that baseline from a value in my database table (the table perhaps has population values from 1900 - 2007).

       

      If not a SQL query, is there another way to do this?

       

       

      Thanks.

        • 1. Re: Calculation from SQL query
          Ross Bunker

          Well, it really depends on exactly what you are trying to do.

           

          One way that may work for your scenario is to create an 'Adhoc group' using your date field.  Group all the 1970-1975 dates into one group and leave the other values alone.

           

          Then, you can create a table calculation specifying '% difference from First' and make sure that your 1970-1975 group is first in the visualization (by manually sorting if necessary).

           

          There are other ways to do it as well using custom SQL in the connection dialog that my be more flexible if the above does not work for your scenario.

           

          :)ross

          • 2. Re: Calculation from SQL query
            Marc Rueter

            Here is an example of what Ross was describing with US census data.  I created an ad-hoc group to combine 1960-1980 data.  Then I added a table calculation by right clicking on the measure.  This was a 'percent difference from' calculation based on the 'first' entry.

             

            A couple of other nifty analyses are added as well.

            • 3. Re: Calculation from SQL query
              Kim Rees

              Thanks, guys! I think this is what I'm trying to do. I will try to replicate this in my own data to see if I can get it to work.

              • 4. Re: Calculation from SQL query
                Peter Thomsen

                Some of it can also be done with data blending - you may have to use a calculated dummy field for blending

                 

                Create a dublicate of your data connection

                you may have to tweek the data relationsships to only include relevante fields

                Make sure not to use the year for blending

                Create a calculated field with you base year/years

                 

                in your primary datasource create a calculation like:

                PrimaryDatasource.Population / SecondaryDatasource.CalculatedBaseYearPopulation

                 

                //Peter