2 Replies Latest reply on Oct 2, 2014 11:32 AM by keith.r

    Filtering, maybe using a calculated field? (newbie question)

    keith.r

      I am trying to create a worksheet filter that only shows one "day" of data, and am trying to create a dynamic filter so that the report will always show the most recent day of data in our cube when anyone opens the report from Tableau Server (eg they don't have to change a quickfilter to select the date)

       

      Here are some relevant features of my data:

      • Period Label- I already have a worksheet filter to only return data that is labelled "MTD". This filter safely ignores future dates as well as all historical data because only current 'month to date' days have the MTD flag (this is assigned/updated during the nightly data refresh in the cube).
      • Within that subset of MTD data, I want to only show data associated with the latest/highest "Workday", a field that increments for each billable day. So if today was billing day 5, the data would have been refreshed through day 4, and I would only want to show day 4 on my worksheet. I can't just use MAX(Workday) because the placeholder values in the date table have dates all the way to the end of the month, I need to incorporate the Period Label = MTD into that max.

       

      I tried various formulas, like   MAX(IF([Current Period]="MTD") then [Workday] END)

      but I tend to drop the calculated fields into the sheet to see if they work, and then they calculate at the line level and don't show me the true maximum for the whole data set.

       

      What is the best way to build this using Tableau, for someone who doesn't know SQL language? I'm hoping this is just a calc field that I can then add as a page filter, but am open to all options

       

      Thanks!

        • 1. Re: Filtering, maybe using a calculated field? (newbie question)

          Hey Keith R,

           

          I am also a newbie to Tableau Desktop so this may be totally unhelpful but this quick start guide about relative data filters seems to accomplish what you're trying to do:

           

          Relative Date Filters

           

          if you have any issues, try uploading a packaged workbook (.twbx) and other users will be more inclined to help you work through the issue.

           

          Good luck :-)

           

          Diego

          • 2. Re: Filtering, maybe using a calculated field? (newbie question)
            keith.r

            Diego, thanks for the reply.

             

            I had seen the Relative date filters, but was not sure that will work for this situation? I assumed that it was relative to the current date (today(), today()-1, etc.) The challenge for my particular situation is that the number of relative days may not be constant, but still needs to be limited to one date - the most recent workday/billing day in the data table (time dimension) which could be the prior weekday (today-1), a Friday to Monday split (today-3) or something else if there is a holiday. The table below will help explain it a lot better.

             

            For this example, let's say today is 9/15/2014. The top half of the table below shows the MTD attribute that I am already filtering (because this also filters out future dates and everything before 9/1/2014). Now I need to somehow define the highest date that has the MTD attribute (9/12/2014), or the highest Running Bill Day (9) regardless of actual invoice fact table data, and use that result as a filter and only show records with a date of 9/12/14 or bill day of 9 in the view.


            Thanks for your continued support and suggestions!

             

            Table1: the date dimension table format

            Table2: an example of what a report looks like

             

                

            Calendar DateBilling DateRunning Bill DayCurrent Pd Label
            2014-09-019/2/2014 0:000MTD
            2014-09-029/2/2014 0:001MTD
            2014-09-039/3/2014 0:002MTD
            2014-09-049/4/2014 0:003MTD
            2014-09-059/5/2014 0:004MTD
            2014-09-069/5/2014 0:004MTD
            2014-09-079/5/2014 0:004MTD
            2014-09-089/8/2014 0:005MTD
            2014-09-099/9/2014 0:006MTD
            2014-09-109/10/2014 0:007MTD
            2014-09-119/11/2014 0:008MTD
            2014-09-129/12/2014 0:009MTD
            2014-09-139/12/2014 0:009MTD
            2014-09-149/12/2014 0:009MTD
            2014-09-159/15/2014 0:0010NA
            2014-09-169/16/2014 0:0011NA
            2014-09-179/17/2014 0:0012NA
            2014-09-189/18/2014 0:0013NA
            2014-09-199/19/2014 0:0014NA
            2014-09-209/19/2014 0:0014NA
            2014-09-219/19/2014 0:0014NA
            2014-09-229/22/2014 0:0015NA
            2014-09-239/23/2014 0:0016NA
            2014-09-249/24/2014 0:0017NA
            2014-09-259/25/2014 0:0018NA
            2014-09-269/26/2014 0:0019NA
            2014-09-279/26/2014 0:0019NA
            2014-09-289/26/2014 0:0019NA
            2014-09-299/29/2014 0:0020NA
            2014-09-309/30/2014 0:0021NA

             

            Current report:

                                       Sales         Date

            Customer A          350           9/5/14

            Customer B            15           9/12/14

            Customer C          110           9/11/14

             

            Desired report:

                                       Sales         Date

            Customer B            15           9/12/14