4 Replies Latest reply on Aug 6, 2013 1:55 AM by victor.dertiano

    Show data from the last day of each month

    victor.dertiano

      Hello,

       

      How could I show in a bar chart the data from the last day of month?

      I have data from every day of each month but I want to show just the data from the last day.

      I have a filter that is a calculated field from a date field. The calculation is

      DATEADD( 'day', 1, [DATE_MONTH] ). So I select the day 1 in the filter.

      It works perfectly for the end months. But it doesn’t works with the current month.

      Could you help me?

       

      Thanks!

        • 1. Re: Show data from the last day of each month
          Joshua Milligan

          Victor,

           

          You might want to check out the LAST() function.  I think it may help you in this case.  I'd love to give a more specific answer, but it will depend a lot on your data and the visualization you are creating.  Would you be able to share a packaged workbook (with sample data) or mock something up using the Superstore Sales sample data included with Tableau?

           

          Regards,

          Joshua

          • 2. Re: Show data from the last day of each month
            victor.dertiano

            Could you show me how to use Last() function in this case? It could be a good solution!

            Thanks in advance!!

            • 3. Re: Re: Show data from the last day of each month
              Joshua Milligan

              Victor,

               

              I've attached a workbook that shows how it works.  The great thing is that it matches your chart exactly, except that it also now includes July!

               

              Here are the basic steps:

               

              1. Create a calculated field [Is Last] that will be true if it is the last:

              Last() == 0

               

              2. Add the Date field to the view at the level of Day.  It can be placed on Columns immedately after Month.  This is necessary as the Last() function will be calculated at the Day level.

               

              3. Add that field to the view (I added it to color first to demonstrate how it is working).  Then right click and "Edit Table Calculation..." and set it up so that it is calculating Last() along day and restarting every month.  This means that Last() will equal 0 when it is the last day (where there is data -- so even the 2nd, 13th, or 27th) of the month.

               

              Last Day.PNG.png

               

              When placed on color, it looks like this -- you can see that the last day is orange.

               

              Last Day 1.png

               

              4. Move the [Is Last] field from color to Filters and keep only the True values.

               

              5. This will give you a bar chart with a bar for each month and only the last day of the month (all other days having been filtered out in step 4).  If you don't want the day label to show, you can right click the Day field in Columns and uncheck "Show Header".  You should now have something that looks like this:

               

              Last Day 2.png

              2 of 2 people found this helpful
              • 4. Re: Show data from the last day of each month
                victor.dertiano

                Hey! It works perfectly, thank you very much!!

                Now, if want a more difficult challenge…I have another workbook! It is very similar but with some differences. I have a bar chart that shows data from all the days of each month and it is right. However, what I need is to show the green line using just the last day of each month in its calculation.

                If you see the calculation of CHURN_PERIOD, it works good when I have update date, because I use de function Today(). The problem is when I don’t have updated data.

                Could show me another way to calculate this green line?

                Thanks in advance! I am so grateful!!