8 Replies Latest reply on Oct 26, 2016 11:50 AM by nicholas.riebe

    Question on how to select data from most recent date

      Hi,

       

      I'm now using tableau to build my report, however, I'm experiencing some difficulties.

       

      IDDateSalesPrice
      1Jan-1210015
      1Feb-1211015
      1Mar-1211015
      2Jan-123016
      2Feb-124016
      2Mar-125016

       

      I have a data set like above, and will getting more data by adding more rows under each ID each month (i.e. in April, Each ID will have additional row for Apr-12 with related sales and price data).

       

      I'm now trying to build simple bar chart only for "most recent" month(i.e. I want to display in my chart will only display March data, not Jan / Feb data). However, beside put all data by each month and then manually exclude from the chart, I can't find the way to only display most recent month data.

       

      Could anyone help me to solve this problem?

       

      Thanks!

        • 1. Re: Question on how to select data from most recent date
          Tracy Rodgers

          Hi Wonjoon,

           

          There are a couple of possible solutions. If the most current data will be relative to whatever 'today's' date is, then the following calculation can be used:

           

          month(Date)=month(today())

           

          Then, place this on the filter shelf and select True.

           

          However, if you want just the most recent data (and that may have nothing to do with whatever the month of today is), then a calculation similar to the following can be used:

           

          if DATEPART('month', attr(Date) )=total(max(month(Date))) then 1 else 0 end

           

          Place this on the filter shelf and filter out the 0s.

           

          Hope this helps!

           

          -Tracy

          4 of 4 people found this helpful
          • 2. Re: Question on how to select data from most recent date
            Fred Arve Fahre

            Edit: I have a pretty similar issue. My data updates monthly, and for a few dashboards i want to show the last values.

            E.g. number of customers per last update. The problem is that its not updated on a 100 % regular date, but always a bit into the new month. Of course previous month is not working since it gives a blank when in a new month but the data is still not updated. When I try this one:

             

            if DATEPART('month', attr(Date) )=total(max(month(Date))) then 1 else 0 end,

             

            it gets me the last month of each year (e.g 31/12/2012), but not the last month of current year (since that month is < 12). I have made a similar calc with year in stead of month, but when combining the two they fetches month 12, year 2013, which results in a blank.

             

            What i want is always show the values from the last date, which is of this format.

             

            31/10/2012

            30/11/2012

            31/12/2012

            31/01/2013

            28/02/2013

            31/03/2013

            30/04/2013

            31/05/2013

            30/06/2013

            31/07/2013

            31/08/2013

             

            Do you have any idea how I can accomplish this, so i dont have to manually edit the month every now and then?

            (Edit: If I just add a basic filter, Year = 2013, it seems to work well. But of course it is not 100 % dynamic when it comes to new year).

            Thanks!

            • 3. Re: Question on how to select data from most recent date
              eli.goldner

              Tracy,

               

              I have the same issue...

              I would like to have the date filtered to the month of the most recent date, though the formula which you provided isn't working for me. I get an error stating: Unknown function attr called.

               

              Any help is appreciated.

               

              Thanks,

               

              Eli

              • 4. Re: Question on how to select data from most recent date
                alejandro.molina

                Hi @tracyfitzgerald:

                I have used your calculation and work pretty good for the deepest level. Let me show you.

                I have the following dataset.

                MonthIDEIDCPI
                201401Project A1.17
                201402Project A1.2
                201403Project A1.11
                201404Project A1.11
                201405Project A1.11
                201408Project B0.84
                201401Project C0.9
                201402Project C0.9
                201403Project C0.92
                201404Project C0.97
                201405Project C0.4
                201401Project D1.99
                201402Project D1.97
                201403Project D1.9
                201404Project D1.9
                201405Project D0.65
                201406Project D0.68
                201407Project D0.68
                201408Project D0.68
                201409Project D0.68
                201402Project E4.08
                201403Project E1.48
                201404Project E1.48
                201405Project E1.48
                201406Project E1.52
                201407Project E1.68
                201408Project E1.61
                201409Project E1.61
                201410Project E2.06
                201408Project F1.66
                201409Project F1.66
                201410Project F1.32

                 

                If I have a filter based on the column "EID", the formula "if DATEPART('month', attr(Date) )=total(max(month(Date))) then 1 else 0 end" works well, showing only the results for the most recent date.

                For example, for filter EID=Project A, then CPI = 1.11. for filter EID=Project D, then CPI = 0.68


                Now, if I want to show all the registers (filter by All), the formula "if DATEPART('month', attr(Date) )=total(max(month(Date))) then 1 else 0 end" does not work as I expected, since it only shows the following registers:

                MonthIDEIDCPI
                201410Project E2.06
                201410Project F

                1.32

                 

                I was expecting:

                MonthIDEIDCPI
                201405Project A1.11
                201408Project B0.84
                201405Project C0.4
                201409Project D0.68
                201410Project E2.06
                201410Project F1.32
                AVG1.07

                 

                Any ideas to fix this issue?

                 

                Thanks in advance

                • 5. Re: Question on how to select data from most recent date
                  Stephan Lorenz

                  Sorry if I am digging up corpses...

                   

                  Alejandro, did you manage to solve your problem? I am facing a similar situation.

                   

                  Thank you

                  Stephan

                  • 6. Re: Question on how to select data from most recent date
                    alejandro.molina

                    Hi Stephan:

                    Yes, I figured it out.

                    If you follow my example above, I just modified the original formula, as follow:

                     

                      IF [MonthID] = { INCLUDE [EID] : MAX(IF NOT (ISNULL([CPI])) THEN [MonthID] END) }

                        THEN

                        [CPI]

                        END

                    • 7. Re: Question on how to select data from most recent date
                      Teji Adeyemon

                      I have not exactly but a similar Max Date problem in several workbooks and have been struggling for a few months now with how to resolve in Tableau-- for every formula that I tried, I kept getting all kinds of "can't mix this and that" errors.  Instead, I have been dumping the data into Excel and resolving with a formula there but the extra step was bugging me.  This put me on the right track to solve my problem and has helped me immensely!

                      • 8. Re: Question on how to select data from most recent date
                        nicholas.riebe

                        Teji,

                        I had a similar need where my data looked like this:

                         

                         

                        Customer ID# of OccurStart Date# of Wks
                        555555555506/24/163
                        555555555507/22/161
                        555555555508/05/161
                        555555555508/26/161
                        555555555508/26/162
                        555555555509/16/161
                        555555555509/16/162
                        555555555509/16/163
                        555555555509/16/164
                        555555555509/16/165
                        444444444406/10/165
                        444444444408/05/161
                        444444444408/19/161
                        444444444408/19/162
                        444444444409/16/161
                        444444444409/16/162
                        444444444409/16/163
                        444444444410/01/161
                        444444444410/01/162

                         

                         

                        And I needed it to display the last record for each customer (in other words, the max start date and # of weeks associated with that start date).

                         

                        Customer ID# of OccurStart Date# of Wks
                        555555555509/16/165
                        444444444410/01/162

                         

                        Here is what I did to solve:

                         

                        1. Create a calculated field

                        if [Start Date] = {FIXED [Customer ID]: max([Start Date])}

                        then 1

                        else 0

                        END

                         

                        2. Filter on the calculated field = 1

                         

                        3. Right click on the '# of weeks' pill, and change to a MAX measure.

                         

                        Viola!