2 Replies Latest reply on Sep 12, 2016 9:02 AM by Béatrice LORIOT

    Min date accross multiple filters

    Béatrice LORIOT

      Hi Everyone,

       

      With the following data :

      CountryDateQuarterEngine SearchKeywordPosition

      including mutliple countries, months, quarters, engine searches (Google, Yahoo) i am evaluating the position of my website keywords from 0 to 9.

       

      Meaning when people search for the Keyword "blue", my company appeared in position 0 to 9 in the engine search result pages.

       

      I am looking for the first time a keyword appeared above position 0  which means this is the first time during the period that the keyword "blue" appeared in the results pages and drove people to my website.

       

      My filters are

      Country     Quarter     Month     Engine search

       

      and each time I do a selection / multiple selection, I want to know which keywords are new / appeared for the first time in the position above 0 during the selected period

       

      Average will be used to calculate position as keyword can be in position 1 in March with Google, and in position 3 in March with Yahoo.

       

      I have had long conversation with few Tableau experts and we can't find the solution.

      i am enclosing an xls for test purposes.

       

      Thanks a lot

        • 1. Re: Min date accross multiple filters
          Andrew Watson

          I'm not certain what you want here, what results should be expected, so I'm just going guesstimate.

           

          Create this calculated field to identify the first date each keyword/month/quarter/search engine/country combo appeared with a position above 0 for the first time:

           

          { FIXED [Keyword], [Engine Search], [Country],[Quarter],MONTH([Date]):MIN(IF [Position]>0 THEN [Date] END)}

           

          Put your filters into Context

           

          Country Quarter Month Engine search

           

          Drag your calculation above to the filters as well, no need to be in Context, filter for Relative Dates, Special and non-null values - i.e. you're filtering out instances that aren't the first instance for your selection the keyword appears above 0.

           

          That will give the following average positions for the month of July:

           

           

          One thing to be careful of is when a year changes, you'll need to account for that as well.

           

          This was done in Tableau 10. If you're using Tableau 9 then the MONTH([Date]) within the formula will have to be created as a seperate calculated field and that new field referenced instead.

          2 of 2 people found this helpful
          • 2. Re: Min date accross multiple filters
            Béatrice LORIOT

            Bonjour and thanks everyone for helping out.

             

            The solution that best meets my request is the following :

             

            First create a calculated field  :

             

            { FIXED [Country], [Keyword] :

            MIN(

            IF [Position] >0

            THEN [Date]

            END

            ) }

             

            then create a second one:

             

            IF DATETRUNC( 'month', [Date] ) = DATETRUNC( 'month',

            [ccc])

            THEN "New"

            ELSE ""

            END

             

            Use the second one as a filter and select "New only" and put to context.

            In column : Country

            In Rows : Keywords, and the 2 calculated files which you can hide (Do not show header)

             

            Then it becomes easy to filter per period, country (separately or together), etc to find out when a keyword appeared first.

             

            Thanks again,

            KR

            Béatrice

            1 of 1 people found this helpful