2 Replies Latest reply on Jun 1, 2012 5:55 PM by rishi.misra

    Date filtering inside of a calculated field

    Mark Sheraton

      Hi I'm new to Tableau but am impressed with its ease of use.

      However I'd like to know if the fucntionality can do the following (as I can do in Qlikview with set anaylysis)

      (see the attached file, sheet 1 is the dimension (advert) and sheet 2 is the Facts (delivered impressions)

       

       

       

       

      Couple of variables / paramters.

      vMinDate = 01-Aug-2011

      vMaxDate = 31-Dec-2011

       

      I'm only interested in Adverts that have delivered impressions during November (or any other defineable period)

      For each Ad I want to see total impressed delivered since the Start of the Ad until the end of Nov.

       

      So I apply the filter [Transaction Date] = Nov

       

      In the Sum calcation I need to do the following.

       

      for each Ad.

      Sum(  

          Condition 1.

              Clear the current filter [Transaction Date] = Nov

       

          Condition 2.      

              [Transaction Date]  between vMinDate (01-Aug-2011) and Max([Transaction Date])

        

          Condition 3.

              Not([Ad End Date] <= Min([Transaction Date]))

                  Excludes Ad 5 before it ends before 01-Nov-2012

       

          Condition 4.

              Not([Ad Start Date] >= Max([Transaction Date])) )

                      Excludes Ad 4 (starts after 31-Nov-2012

        

          Now sum Impressions

          )  

       

      Regards

      Mark

        • 1. Re: Date filtering inside of a calculated field
          Alex Kerin

          I think this does what you need. I created a parameter with a value 1 through 12 and gave an alias for these Jan through Feb. I showed the parameter control (drop down). I have a calc that goes line by line and copies the date column over only if the current row month is equal or less than the parameter.

           

          There's a window calc that checks the maximum date for an AD ID and sees if it occurs within the month chosen. If not, that AD ID is not shown

           

          Finally there's a window sum of the impressions for all impressions prior and including the current month. This is set to partition on Ad ID.

           

          Now, my knowledge of window_calcs is lower than others, and this may not be as robust as you may need. Others could have a much simpler solution...

          • 2. Re: Date filtering inside of a calculated field

            @Alex Kerin - sounds like you've had some experience with setting a date range within a calculated field. I would very much appreciate it if you could take a look at what I'm trying to do below and let me know if you have any insights:

             

            So here's my data structure:

             

            Tab name: Today's appointments

             

            Customer id       TODAY's Appointment start time

                                    8 am      9 am                         10 am       11 am       12 pm ....................

            001

            002                                 Past No Shows: 2

             

             

            My data also has a field called "IsNoShow" which tallies the number of  "No Shows" for each customer. Here's the challenge - how do I flag customer's who have "No Showed" in the past for Today's appointments? So, for example, as shown above, I'd like customer 002, who has an appointment at 9 am today, to be flagged with 2 previous No Shows.