11 Replies Latest reply on Jun 1, 2016 2:16 PM by Joe Oppelt

    Date Filtering: Hiding Incomplete Months (Not based on TODAY())

    John T

      Hi all, trying to figure something out regarding the filtering of dates and hiding certain dates.

       

      In this example, I have two data sources: Daily Data (which is published everyday) and Monthly Data (which is published after the months end)

       

      What I will be displaying on a data table is the daily average and monthly average for The Last Month, The Last 2 Months, and the Last 3 Months. (I've also mocked up what I want it to look like in Tableau in the attached spreadsheet, along with the correct values)

       

      However, what I want Tableau to do is to not include any partial months (where the data is not yet finalized and available) For example, in my spreadsheet, I have 5 entries for July, which I was excluded from any calculations. Therefore, the Last Month entries will include only May, the Last 2 Months entries will include May and June, and the last 3 months entries will still only include May and June (Since July data hasn't been finalized and there's no data before May)

       

      In theory, the logic should be that only data should be included for the last monthly entry. However, since they're in two different data sources, this makes it a bit challenging.

       

      Please let me know if this makes sense, thanks!

        • 1. Re: Date Filtering: Hiding Incomplete Months (Not based on TODAY())
          Joe Oppelt

          Will you ALWAYS have data on the last date of any particular month?  (Suppose the last day of some month has no transactions.)

           

          How do you know when a month is actually finalized?

           

           

          Is it safe to say that if you have a row in the monthly sheet, then the month is finalized?

          • 2. Re: Date Filtering: Hiding Incomplete Months (Not based on TODAY())
            John T

            Joe -- thanks for replying.

             

            For the monthly data, yes, if there's a row for the date, then it means that the month has been finalized and the value has been verified.

             

            In terms of daily numbers, it doesn't always fall on the last day of the month. For the sake of the sample data, I included a value for each day, but if the last day of a month is a holiday or weekend, there would not be a row for it.

             

            Also, the way it works is the monthly data is always finalized a couple days after the month end, so if there's a row for July 2016, it can be assumed that all the daily values for July have been inputted into the database.

            • 3. Re: Date Filtering: Hiding Incomplete Months (Not based on TODAY())
              Joe Oppelt

              9.0 workbook attached.

               

              One thing I might be doing wrong is computing the average.  I'm just taking the average of the averages, but I guess I should probably be taking the sum of the individual daily values and divide by the number of rows.

               

              But this is the approach you can use if you are going to do this in separate data sources.


              Sheet 1 just shows how to join the individual data sources to get the daily averages.


              Sheet 2 shows how to create your calcs to do what you want with the daily averages.

               

              Sheet 3 shows how to get a single column of numbers for daily averages.

               

              Sheet 4 shows the same thing for the monthly values.  (I can't see a way to put both on the same sheet because there isn't a dimension to use to make different columns.)

               

              Dashboard shows sheets 3 and 4 combines for one visualization.

               

              If this is getting you in the right direction, we can work on adjusting the calcs for the daily averages if I wasn't doing the right arithmetic there.

              • 4. Re: Date Filtering: Hiding Incomplete Months (Not based on TODAY())
                John T

                Joe, thanks for taking the time to help out with this -- I really appreciate it!

                 

                I'm less worries about the arithmetic, as in my real data, I've actually gotten the math to work out (it's slightly more complicated than just getting the AVG). My biggest issue has been the whole logic behind only using daily dates that are earlier than the last approved monthly date.

                 

                I reviewed your workbook and the solution is great, but the only issue is that in my real data, there's actually another layer of complexity in that we could have multiple entries for a single date (because my data has different lines of businesses) For example, Group A, Group B, Group C, Group D, would all have an entry for June 3rd, 2016 -- which is why using the ATTR() function isn't working because it will come out as null since there are multiple entries with the same date.

                 

                Any advice on how to overcome this?

                • 5. Re: Date Filtering: Hiding Incomplete Months (Not based on TODAY())
                  Joe Oppelt

                  Hack up the "Daily" sheet from this excel file to show what you have.

                  • 6. Re: Date Filtering: Hiding Incomplete Months (Not based on TODAY())
                    John T

                    Joe,

                     

                    Please see the updated attached. For simplicity's sake, I divided the data into two groups "A" and "B". There are now duplicate date entries for a few days were both A and B have a value.

                     

                    What I want to show now is (for Dailies):

                     

                    Last Month Avg for A

                    Last Month Avg for B

                    Last 2 Month Avg for A

                    Last 2 Month Avg for B

                    Last 3 Month Avg for A

                    Last 3 Month Avg for B

                     

                    Same logic as before -- do not include any daily data for days where the month has yet to be approved (aka no entry in the Monthly)

                     

                    Thanks again!

                    • 7. Re: Date Filtering: Hiding Incomplete Months (Not based on TODAY())
                      Joe Oppelt

                      And what do you want to do for the MONTHLY averages?  Still a single number as it is in the MONTHLY sheet?  If you had A and B categories in the MONTHLY sheet, you could join on that and break it apart easily.  If you did NOT want to do that, then I can do stuff in the secondary sheet, but it gets messier.

                      • 8. Re: Date Filtering: Hiding Incomplete Months (Not based on TODAY())
                        John T

                        Hi Joe, for monthly, yes, still the single number. Let's say that value is the same for both groups.

                        • 9. Re: Date Filtering: Hiding Incomplete Months (Not based on TODAY())
                          Joe Oppelt

                          See attached.  I just modified Sheets 2 and 3 to show what to do with the secondary data source.

                           

                          Actually, I would consider reshaping the data in the daily data to have multiple values (by category) in each row.  The calcs I made in the daily data source just simulates that.  But if you have 100 different category values, it could get really messy.

                          • 10. Re: Date Filtering: Hiding Incomplete Months (Not based on TODAY())
                            Joe Oppelt

                            Know what?  I just realized something.  I'm going to play with this making the daily data the primary source.  Please hold...

                            • 11. Re: Date Filtering: Hiding Incomplete Months (Not based on TODAY())
                              Joe Oppelt

                              See Sheet 5 in the attached.

                               

                              Daily is now the primary source.

                               

                              Edit the filter for MY(Date).  (Notice that the filter is in the secondary source.)


                              I excluded NULL.  That means that if the row for July is not there, the July daily data will not show.  (Take the filter off to see what I mean.  back-arrow will put it back on.)

                               

                              Now all the principles I applied in calc-ing the last month, total-two-months, and total-three-months can be applied here.  You'll have to rewrite the calcs to look at the sums and averages in the primary instead of the secondary source, but the way you'll do it will be similar.  You just won't need [Value A] and [Value B] any more.  And no matter how many groups you have, it will automatically do it for you because Daily (where all the moving parts are) will be the primary source.