14 Replies Latest reply on Feb 5, 2013 3:57 AM by brian.comeau

    Calculated Field With Where Clause?

    brian.comeau

      Hi All,

       

      I haven't seen this anywhere else, and perhaps I'm 'breaking' Tableau 8.0, but I would like to do something like the following:

       

      table Sports Matches:

      • Sports Match
      • Date From
      • Date To

       

      table Viewers:

      • Date
      • Network
      • Viewers

       

      1. Parameter As List of Sports Match from table Sports Matches
      2. Select 'Superbowl' from Parameter
      3. Display 3 fields - Avg. Daily Viewers for 7 days before Date From, Avg. Daily Viewers between Date From and Date To, Avg. Daily Viewers for 7 days after Date To

       

      So far this has eluded me.

       

      Any suggestions?

       

      Thanks,

      Brian

        • 1. Re: Calculated Field With Where Clause?
          Ben Porter

          I think you could get the result you are looking for by exposing sport match as a quick filter.  Do you have an example you could post?

          • 2. Re: Calculated Field With Where Clause?
            brian.comeau

            Hi Ben,

             

            That might be a solution if I was only looking at the viewers for that particular sports match. However, what we ultimately want to find out is the total viewers across all networks. If that sports match is only on one network, a quick filter on Sports Match will exclude all other networks.

             

            I can think of it in a programming language sense, using:

             

            @Parameter on Sports Match = 'Superbowl'

             

            SELECT MAX(date to) FROM Sports Matches WHERE Sports Match=@Parameter As dtMaxDate

            SELECT MIN(date from) FROM Sports Matches WHERE Sports Match=@Parameter As dtMinDate

             

            Then when we look up our data for tableau, we say something like:

            Pre Period: IF Viewers.Date>=dtMinDate-7 AND Viewers.Date<dtMinDate THEN Viewers.Viewers END

            Post Period: IF Viewers.Date>dtMaxDate AND Viewers.Date<=dtMaxDate+7 THEN Viewers.Viewers END

            Sports Match Period: IF Viewers.Date>=dtMinDate And Viewers.Date<=dtMaxDate THEN Viewers.Viewers END

             

            I should then be able to drag the other dimensions (Network for example) into my viz to see the average viewers by Network during each of those periods, to understand if some have shifted to watch the Sports Match.

             

            I hope that helps!

             

            Thanks,

            Brian

            • 3. Re: Calculated Field With Where Clause?
              brian.comeau

              As an update, I've tried using the RAWSQL_DATE to get the required field, but now I'm running into another SQL error:

               

              Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

               

              I assume this is because I am looking for MAX(Date) and then using Date as my Columns dimension... Not sure how to circumvent this. Again, in SQL I would simply use a subquery to figure out this information. In an object oriented language I would store the MAX(Date) as a variable. I can do neither here.

               

              Any suggestions?

               

              Thanks,

              Brian

              • 4. Re: Calculated Field With Where Clause?
                brian.comeau

                I must have spoken too soon. I just noticed some issues with this one.

                 

                For example, the SQL that is being output in the Log file from Tableau is completely bizarre and nowhere near where I want it to be:

                 

                SELECT DATEADD(day,0,(SELECT MAX(dtTo) FROM tbl_promo WHERE strPromo='Coupon Drop 4' GROUP BY strPromo)) AS [Calculation_4620131164521948], (SELECT MAX(dtTo) FROM tbl_promo WHERE strPromo='Coupon Drop 4' GROUP BY strPromo) AS [Calculation_9600131160547724], (SELECT MIN(dtFrom) FROM tbl_promo WHERE strPromo='Coupon Drop 4' GROUP BY strPromo) AS [test_cal_date_promo_max (copy)], DATEADD(day,(-0),(SELECT MIN(dtFrom) FROM tbl_promo WHERE strPromo='Coupon Drop 4' GROUP BY strPromo)) AS [test_cal_date_promo_postperiod (copy)], MIN([tbl_pmix_test].[dtDate]) AS [TEMP(Calculation_04701311738506730)], MAX([tbl_pmix_test].[dtDate]) AS [TEMP(Calculation_04701311738506731)], SUM(CAST(CAST([tbl_pmix_test].[dblQ] as bigint) as BIGINT)) AS [TEMP(Calculation_04701311738506732)], MIN([tbl_pmix_test].[dtDate]) AS [TEMP(Calculation_06501311739327620)], MAX([tbl_pmix_test].[dtDate]) AS [TEMP(Calculation_06501311739327621)], SUM(CAST(CAST([tbl_pmix_test].[dblQ] as bigint) as BIGINT)) AS [TEMP(Calculation_06501311739327622)], MIN([tbl_pmix_test].[dtDate]) AS [TEMP(Calculation_69201311650485960)], MAX([tbl_pmix_test].[dtDate]) AS [TEMP(Calculation_69201311650485961)], MIN([tbl_pmix_test].[dtDate]) AS [TEMP(cal_promo_markers (copy)0)], MAX([tbl_pmix_test].[dtDate]) AS [TEMP(cal_promo_markers (copy)1)], SUM(CAST(CAST([tbl_pmix_test].[dblQ] as bigint) as BIGINT)) AS [TEMP(cal_promo_markers (copy)2)], SUM(CAST(CAST([tbl_pmix_test].[dblQ] as bigint) as BIGINT)) AS [sum:dblQ:qk]  FROM [dbo].[tbl_pmix_test] [tbl_pmix_test]  WHERE ([tbl_pmix_test].[strCountry] = 'Australia'

                 

                What this should say is something like this: 

                SELECT SUM(dblQ) FROM tbl_pmix_test WHERE dtDate <= (SELECT max(dtTo) FROM tbl_promo WHERE strPromo='Coupon Drop 4') AND dtDate>=(SELECT min(dtFrom) FROM tbl_promo WHERE strPromo='Coupon Drop 4') AND strCountry='Australia'

                 

                That is an excerpt of course and not the whole code, but that is the general idea.

                • 5. Re: Calculated Field With Where Clause?
                  brian.comeau

                  It seems like it might be related to Richard Leeke's posts here:

                   

                  http://community.tableau.com/thread/105636

                   

                  Can't find a solution however, as I'm running into issues when the RAWSQL_DATE is then referenced for an offset with DATEDIFF to compare to another date field...

                  • 6. Re: Calculated Field With Where Clause?
                    brian.comeau

                    Any suggestions from the community on this one, or is it impossible?

                    • 7. Re: Calculated Field With Where Clause?
                      Shawn Wallwork

                      Hey Brian, it's late Sunday (in my time zone) but there are lots of really smart people around world. So maybe if we ping Dimitri or Richard (who are working Monday) maybe you'll get a bit of attention on this. Hope this helps.

                       

                      --Shawn

                      • 8. Re: Calculated Field With Where Clause?
                        brian.comeau

                        Hi Shawn,

                         

                        Thanks for pinging those two. I forgot how to do that, otherwise I would have seen if Jonathan, Joe, or even Ross might have some input.

                         

                        The majority of you guys are back in North America, so I can certainly understand the time-differences.

                         

                        Thanks,

                        Brian

                        • 9. Re: Calculated Field With Where Clause?
                          Dimitri.B

                          Brian, there might be an easier way, without resorting to RAWSQL_DATE...

                          Do you have any sample data?

                          • 10. Re: Calculated Field With Where Clause?
                            brian.comeau

                            Hi Dimitri,

                             

                            I've attached an excel workbook that contains two tabs, both with the data that could be used.

                             

                            Thanks,

                            Brian

                            • 11. Re: Calculated Field With Where Clause?
                              brian.comeau

                              Joe Mako, Jonathan Drummey, Richard Leeke, perhaps this is related to the date densification discussion that happened last week? I watched the webinar and some of the concepts hinted that this might be possible using that, but ultimately I'm still just as lost as I was last week.

                               

                              Any suggestions? I included an extract of the two relevant tables and I'm using SQL Server 2008 R2 as a backend, thus the pass-through queries.

                               

                              Thanks!

                              • 12. Re: Calculated Field With Where Clause?
                                brian.comeau

                                And that is that... I was able to solve it (perhaps not the most elegantly) using data densification.

                                 

                                Duplicated the data source, mapped a date for every potential instance of to/from and re-joined.

                                 

                                Thanks to Joe for his webinar last week that inspired this idea.

                                • 13. Re: Calculated Field With Where Clause?
                                  Jonathan Drummey

                                  Congratulations on figuring it out!

                                  1 of 1 people found this helpful