2 Replies Latest reply on Jan 10, 2013 10:38 AM by Matt Lieberman

    Counting Number of Months Where Values Greater Than 10,000

    Matt Lieberman

      I am trying to create a calculated field that determines the number of months a particular ad placement registered over 10,000 impressions for that month. I created a calculated field called “Months Live” with the syntax “DATEPART('month', [Date]) to create a metric that listed how many months a particular placement was live and changed the data aggregation to “Count(Distinct)” rather than “Sum.” So any month where our SQL database has records for that particular placement is counted in the “Months Live” measure. If a placement was live from January through March of 2012 its "Months Live" value would be 3 and so on. This works fine.


      However, there are many times where a placement will have values in the database for a particular month where it registers very few impressions and essentially was not live during the period. Thus, I would like to create a calculated field that acts in a similar function to "Months Live" but only counts a particular month to the total if the placement registered over 10,000 accounts that month. The data table in Tableau is aggregated over 2 years. I am pretty I am able to do this in Tableau but I am having trouble getting the syntax for the field correct. Any help is greatly appreciated.

        • 1. Re: Counting Number of Months Where Values Greater Than 10,000
          Mark Holtz

          Hi Matt,


          How is the view constructed where you want to show this?


          From what you describe, I assume the view breaks down by the "ad placement" dimension (creating a Months Live calculation) for each distinct one. And you are saying you'd like to create another calculation for each one as MonthsOver10k.  How are you incorporating the date (month) dimension?


          Also, not sure if you'd like to plan around it, but the [Months Live] logic you described will only work within 1 calendar year, since there are only 12 distinct month-datepart values.

          Can you share a packaged workbook? or at least a screenshot and sample data source?

          • 2. Re: Counting Number of Months Where Values Greater Than 10,000
            Matt Lieberman

            Hi Mark,


            Thanks for offering to help. I realize that my current "Months Live" calculation only works for one year, but thankfully the placements are at the end of a Campaign-Publisher-Placement hierarchy and each year has a different campaign associated with it so the calculation gets the job done for now. Though I would be interested in learning about any way to get around that and create a calculated field that is able to generate months live for greater than 12 months. I can't do a basic "Count" because many placements have multiple values for the same date and thus the same month.


            The view is a table with a hierarchy from Ad Campaign, Site, to Placement, followed by several metrics. I have attached a picture of how the data is laid out. There is no column in the table for any date fields in the table.


            You are correct in that I want a field that acts exactly as "Months Live" that only "counts" a month if the placement registered over 10,000 impressions during the period.


            The data source is a MySQL database which compiles daily data from various reporting services into one centralized database. The date field can be broken down to a daily level and many placements have multiple records in the database for a single day.


            Thanks again for trying to help. Please let me know if you would like any further clarifications or have any other questions.