13 Replies Latest reply on Feb 24, 2017 2:59 PM by Vasu choudhury

    Display 12 month view (current + 11 previous months)

    Teresa Wright

      I have a simple chart that tracks one measure by year/month.  I would like to alter this chart to show the current month (Jan 2017) and the previous 11 prior months (Feb 2016 - Dec 2016).

      I believe I would use either a filter or a parameter, but I am not sure which is the best/easiest to use.

      Sorry I cannot share actual data due to work restrictions.

       

      How would I show just Feb 2016 - Jan 2017?

      Current Mo + Previous 11 Mo Display.jpg

      Thanks!

        • 1. Re: Display 12 month view (current + 11 previous months)
          Joe Oppelt

          What determines current month?  Calendar?  (Today is February, for instance, not January, as your problem statement mentions.)

           

          Actually, you could have an LOD calc that does:

           

          { FIXED : MAX([Date Recorded]) }

           

          That would tell you the last date in that field in your database before any filters are applied.  Then you could use that to build the start month (well, actually the end month) of your filter.  (I use that sort of approach in a lot of my apps.  Regardless of today's date, we might not yet have February data in the data source, so the most recent month is still January in those applications.)

           

          But maybe you want the users to be able to say, "I want to see 12 months, up to December 2016..."  then I would have a parameter the user can set, and build my "latest month" from there.

           

          Is your data recorded with daily dates?  Or are all January dates stored with Jan 1, 2017?  (We have an application with a data source like that.)  This would impact how you would approach building your filter range.

           

          I know you said you can't upload your workbook due to restrictions, but I would hope you could make a  set of bogus data in excel and pare down your workbook to remove anything proprietary, and give us a working example of what you have so we can talk about your specific conditions.  So much of a question like yours is dependent on your data layout, the way you are using it, and what you really need to accomplish with it.

           

          There is a short video (less than 10 minutes) that shows how you can anonymize your data so you can upload an application:

           

          Anonymize your Tableau Package Data for Sharing

           

          I've been doing stuff on this forum for a while.  I assure you, your answer will be more direct and complete if we have a workbook to work on together.

          • 2. Re: Display 12 month view (current + 11 previous months)
            Teresa Wright

            Joe,

             

            In my case, current month = the last full of data.  January has closed, so it is the current month.

            Dates are stored in MM/DD/YYYY format.  I will see about getting a mock set of data to post.  Thanks!

            • 3. Re: Display 12 month view (current + 11 previous months)
              Teresa Wright

              Adding a mock file and new screen shot.  Hope this helps make it clearer.

              Current Mo + Previous 11 Mo Display.jpg

              • 4. Re: Display 12 month view (current + 11 previous months)
                Joe Oppelt

                So you can't really grab the last date in the data because it might be in the middle of the month.  Today you probably would have some February data in there.  And I'll bet you can't even say, "If I have data on the last day of the month that is the last date in my data, then I have a closed month..."  If you can, then we can do nice things with that.  (Maybe you only load in data once a day, so if you have Jan 31 data, you have it all for January.)

                 

                See, here's where I am going with this.

                 

                You can determine what your last day should be.

                 

                Consider this:

                 

                DATETRUNC('month', [date field])  -- This takes any date and makes it the first of the month.  (If you had chosen 'year' instead of 'month', it would take any date and make it the first of January of that year.)

                 

                DATEADD('month',1,[date field]) -- this adds one month to any date.

                 

                So this:

                 

                DATEADD('month', 1, DATETRUNC('month', [date field]))-1

                 

                will end up giving you the last day of the month of whatever month is in [date field].  (Working from the inside out, truncate to the first of the month, then add a month, then subtract a day.)  Doesn't matter if the last day of the month in question is the 31st or the 28th, Tableau handles it.

                 

                Using that, if you do the FIXED calc I suggested earlier, then you can check if that value is the last day of the month of that biggest date.  If it is, then you want that month as your 12th month, otherwise you want the prior month as your 12th month.

                 

                I'll stop at this point.  If you only add data once per day to your data source, then this is a workable way to start building your filter.  If you add continuously, then you'll never know if you have the last record to be added on January 31 until a row has Feb 1.  Depending on how your data is built, we can roll with one or the other way to look at your data.

                 

                Once you know what your 12th month should be, then you would have a filter that roughly looks like this:

                 

                IF [Transaction Date] <= [My last date] and

                [Transaction Date] >= DATEADD('month', -11, DATETRUNC('month', [My last date]))

                then 1 ELSE 0

                END

                 

                [my last date] would be the last day of the month you decide is your last month.  And the DATEADD(DATETRUNC(...)) would be the first day of the first month.

                 

                Put that on your filter shelf and select for value = 1.

                 

                You'll only get rows between those dates.

                 

                So the trick is just figuring out how to do the [my last date] calc.

                • 5. Re: Display 12 month view (current + 11 previous months)
                  Joe Oppelt

                  Another point.  Do you mind seeing a stubby February 2017 bar if you only have two days of February data so far?

                  • 6. Re: Display 12 month view (current + 11 previous months)
                    Joe Oppelt

                    (Or in this case, a stubby January since there isn't a full range of January dates in there?)

                     

                     

                    The example is one thing.  Your actual application will likely need to care about this.

                    • 7. Re: Display 12 month view (current + 11 previous months)
                      Teresa Wright

                      Thanks Joe, but I was trying to avoid a calculation.  I wanted to create a simple view where I choose the start/end dates, and I cannot show anything in Feb.  I will keep at it and see if I can find any videos on YouTube.

                      • 8. Re: Display 12 month view (current + 11 previous months)
                        Joe Oppelt

                        Well here it is in 2 minutes.

                         

                        I put the two calcs (max date and end date to grab) in the title so you can see what it does.

                         

                        And the filter gets what you need to see.

                        1 of 1 people found this helpful
                        • 9. Re: Display 12 month view (current + 11 previous months)
                          Joe Oppelt

                          And here, attached (sheet 2) is just using a simple quick filter, but the user is going to have to change it from month to month.

                           

                          What I wrote in Sheet 1 will work automatically forever.

                          • 10. Re: Display 12 month view (current + 11 previous months)
                            Teresa Wright

                            Thank you for all your hard work, Joe.  However these calculations did not work for me when I tried using them.  For the Max Date I got this error:

                             

                            "Level of detail expressions are not supported by the federated data course (name of query/name of database)

                             

                            Since the other two calculations are dependent on Max Date, those did not work either.  I guess my sample data file was not accurate. I will do more investigation online and see if I can come up with an option.  I hate that what used to be so simple in Excel now takes multiple calculations to achieve.  Thanks anyway!

                            • 11. Re: Display 12 month view (current + 11 previous months)
                              Joe Oppelt

                              The problem with expecting Tableau to do everything excel does is that Tableau has to be all things to all data platforms.

                               

                              I was aware that some data platforms didn't support LOD calcs, but I have never encountered one.

                               

                              So I used a different hack to determine the max date in the data source.

                               

                              In the attached (sheet 3), I made a duplicate of your data source.  (Right click on your data source and select "Duplicate".)  then I made a calc called [Another max date].  This grabs the MAX([Date Recorded]) from the copy source.  To make it work the way I need, I disconnect all blend connections.  (When you are clicked in the duplicate, unclick all the orange links:

                               

                               

                              With no blend connections you might get a message from tableau, but you can ignore it.  Without blend connections, Tableau returns whatever aggregation you request across the whole data source.

                               

                              Because this is drawing from a secondary source, the returned value must be treated as a measure.  So you see it in the MEASURS panel instead of DIMENSIONS.  I out that value into the title in place of the original LOD value.  then I made a copy of [End of final month] to use the new calc.  That is also in the title now.  Finally, I made  copy of [Date range...] to use the new calc.

                               

                              I have to point out something here.  Because [Another max date] is an aggregate, [End of final month (copy)] becomes an aggregate.  And we can no longer compare a dimension value (such as [Date recorded]_ directly to an aggregate.  So I wrapped [Date Recorded] in the ATTR() function in the [Date Range...] calc to make Tableau treat it like an aggregate as well.

                               

                              With that, I replaced the original filter calc with the copy, and now it's working for you without using LOD.

                              • 12. Re: Display 12 month view (current + 11 previous months)
                                Teresa Wright

                                Thanks Joe.  The last example was really helpful.  In the end I used a filter, and for now it seems to do the trick.  I've shared your examples with a couple of coworkers.

                                 

                                Date Range.jpg

                                • 13. Re: Display 12 month view (current + 11 previous months)
                                  Vasu choudhury

                                  Use below logic..

                                  Create calc field:

                                  Max Date = {Fixed: Max(Date)}

                                  [Date] is your dimension

                                   

                                  IF DATEDIFF('month',[Date],[Max Date]) <= 11 THEN 'Include' ELSE 'Exclude' END

                                  and drag it to filter shelf to see the results.