1 2 3 Previous Next 32 Replies Latest reply on Aug 22, 2017 1:00 AM by tushar.m

    Latest Month, Prior Month, and Previous year same month

    tushar.m

      Hi Tableau Community,

       

      I am facing a problem and seeking for your help. I've attached a workbook created in v10.3. This report has a drop down to select monthly or rolling. When user selects monthly the other drop down shows values based on individual months. For rolling, the other drop down shows option to select rolling 12 months or 24 months. Now, I want display 3 columns in the view depending on the measure selector.

       

      For example if user selects monthly and then june-2016 then the view should show current month value i.e for jun-2016, previous month may-2016, and same month previous year june-2016. If user selects rolling and then jun-2017, then it should display values for that complete year (July-16 to jun-17) in the current month column, and previous month column should display values from Jul-2015 to jun-2016.  Right now I am only able to show only one column based on the current selection.

       

      Hoping to get some help from such talented community.

       

       

      Regards,

        • 1. Re: Latest Month, Prior Month, and Previous year same month
          Simon Runc

          hi Tushar,

           

          Just had a (very) quick look at this. And just got a couple of questions (so I know what I have to play with solution wise!)

           

          Could the Month-Year selector (so either for Rolling or Actual Month) be a parameter? - This would make this much easier

           

          Not really thought this through, but an option might be to use a Table Calc filter (due to the Order of Operation these are applied last...has the effect of filtering the view, but not the underlying data), as this would let us show the current month, but then also have access to the data for previous months (so Last month and same month last year could be calculated). The downside is that Table Calc filters can't be applied across sheets, so if you plan to have multiple sheets in a dashboard (driven from this filter month) then this wouldn't work. As I said I'm not even sure if this is a feasible solution, but good to know the options!

           

          If you let me know on the above I'll take a look.

          1 of 1 people found this helpful
          • 2. Re: Latest Month, Prior Month, and Previous year same month
            tushar.m

            Thanks for the quick response.

             

            Actually the problem with parameter is that it won't update with the underlying data source. In that case the end users may face problem.

             

            I want to use this as a filter to multiple worksheets  and in addition to this I've 10 more filters in the actual view. So, I don't think table calc will give me the required functionality.

             

            My view should look like this.

             

             

            Let me know if you need more info.

             

            Regards,

            Tushar

            1 of 1 people found this helpful
            • 3. Re: Latest Month, Prior Month, and Previous year same month
              Shinichiro Murakami

              Hi Tushar

               

              I think this is same as Sdhir's one.

              https://community.tableau.com/inbox

               

              I will try to explain step by step.

               

               

              Rolling needs some adjustment.

              + 5 month from normal calendar to your July start fiscal calendar.

              Then add 6 to show it as YYYY-June.

               

               

               

               

              Then, to show last period (last month, same month of last year, last year).

              You need to filter display without filtering underlying data.

              Filtering the View Without Filtering Underlying Data | Tableau Software

               

               

              Last period (last month, or last year based on parameter)

               

               

              Same month last year only for the case month as parameter.

               

               

               

               

              Thanks,

              Shin

              2 of 2 people found this helpful
              • 4. Re: Latest Month, Prior Month, and Previous year same month
                tushar.m

                Hi Shin,

                 

                Thanks for the replay. You are very close the actual solution. However, there are a few things that I would like to discuss with you.

                I want to apply this as a filter to multiple worksheets. Right now, I am not sure whether this is possible.

                 

                I want to show grand total as well. When I added it in the view, this is what I am getting.

                 

                I added two more options in the state or category selector (State contribution and Category Contribution). Here is how I am calculating it (Line 3 and 4).

                 

                This is not giving me the required result. For example,  with State as my selection, consumer has value 8 and grand total is 30. Now when I switch to state contribution then it should give 8/30 =26.6%.

                 

                How to handle these cases?

                 

                Attached is the updated file for your reference.

                 

                Thanks again.

                 

                Regards,

                 

                Tushar

                1 of 1 people found this helpful
                • 5. Re: Latest Month, Prior Month, and Previous year same month
                  Shinichiro Murakami

                  Tushar

                   

                  That's not easy.

                  Relatively easier way to achieve is duplicating data.

                  Otherwise, G.total is always very difficult to control under table calc.

                   

                   

                  Edit data source and duplicate data with union.

                   

                   

                  Change DIM calc to reflect LOD.

                   

                   

                  Show Segment for "Orders"

                   

                   

                  Contribution. I just put on same table.  If you want to separate, put few more conditions in the calculations.

                   

                   

                   

                  Thanks,

                  Shin

                  2 of 2 people found this helpful
                  • 6. Re: Latest Month, Prior Month, and Previous year same month
                    tushar.m

                    Hey Shin,

                     

                    I don't think duplicating data is a feasible solution in my case as it contains 2000k rows and joining 7-8 tables. I have a requirement to display grand total as well in the view. One more thing here is I want to use monthly or rolling as a filter to multiple worksheets. Table calc is not offering me that functionality.

                     

                    It would be very helpful if you could provide some other solution. I am trying to create this from 5 days and still not able to create this one.

                     

                    Regards,

                    Tushar

                    • 7. Re: Latest Month, Prior Month, and Previous year same month
                      Shinichiro Murakami

                      Hi Tushar

                       

                      According to your comments, the request is beyond the level that this community can support.

                      You may need to consider data base management tool rather than challenging everything in Tableau.

                       

                      Thanks,

                      Shin

                      • 8. Re: Latest Month, Prior Month, and Previous year same month
                        tushar.m

                        Ohh

                         

                        If we make the month and rolling selector as a parameter then are there any chances?

                        • 9. Re: Latest Month, Prior Month, and Previous year same month
                          Stuart Fieldhouse

                          Hi Tushar, hi Shin,

                           

                          I hope you don't mind me jumping in here but you seem to have hit a blocker.  I have been looking at this problem in a different way.  Since we are looking at three separate date ranges (current month, previous month and previous year) I decided not to try and filter the list but instead to recalculate the DIM measure within the three date ranges.  I calculate a start date and end date for each range and then use that to work out the DIM values for each period.

                           

                          I have attached a workbook showing my ideas - look at New Sheet.

                           

                          This is by no means a completed solution, but it does seem to tick a number of your boxes - can be applied to multiple sheets, can give totals, can do the difference between month and rolling.  I started working on your first workbook so it doesn't have the extensions you've made to the DIM calculation, but that shouldn't be a problem.  I hope you can use these ideas to develop a complete solution.  Please let me know if there is any more I can contribute.

                           

                          Stuart

                          • 10. Re: Latest Month, Prior Month, and Previous year same month
                            Shinichiro Murakami

                            Hi Tushar,

                             

                            I think so.  As long as you don't mind using parameters, there must be a way.

                             

                            Shin

                            • 11. Re: Latest Month, Prior Month, and Previous year same month
                              Shinichiro Murakami

                              Tushar,

                               

                              Stuart Fieldhouse  already did similar thing, but here is my revised version.

                              Looked like Fiscal year Rolled up number was still missing in Stuart Fieldhouse 's sheets.

                              I took similar approach, but there are preference of how to set formula.

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                               

                              Thanks,

                              Shin

                              2 of 2 people found this helpful
                              • 12. Re: Latest Month, Prior Month, and Previous year same month
                                Simon Runc

                                hi Tushar,

                                 

                                So you have great solutions using a parameter from Shin and Stuart. I think (well I'm 99.9% sure) this is the only way to do this, a filter is a filter (so will filter the data to only that month) and so won't give you access to previous months (although annoying here this is a good thing...95% of times you use a filter you actually do want it to filter the data!)  and we can't use the Table Calculation filter here (which does exhibit this behavior) as we can't apply across sheets (and has issues with GTs). The behavior you want is more akin to a parameter, and the only reason you don't want to use one is that it doesn't dynamically update (which I agree would be a very welcome addition to Tableau). For parameters which contain dates it's not so bad, as you can just populate it with all months into the future, and hope the user is sensible enough to not select June 2019 and expect some data!

                                 

                                On Shin's duplicating data option, 4M rows isn't that big for Tableau, so it might be worth considering or do some extra data work and have the Previous Month and Previous Year values, both actual and rolling, calculated in the data (as columns) ....so when you filter on a month these figures are accessible.

                                 

                                If this were my problem, it would be a toss up between doing the data prep prior to bringing into Tableau, or use a parameter with dates into the future.

                                1 of 1 people found this helpful
                                • 13. Re: Latest Month, Prior Month, and Previous year same month
                                  tushar.m

                                  Thanks Simon, Shin, and Stuart.

                                   

                                  I appreciate your time and effort, and sorry to bother you again and again. Shin and Stuart provided me very good solutions. Now with parameter, I am not able to update the other drop down. So, I have three selectors in the view. This may be confusing for the end users. I am not able to apply this on multiple worksheets/dashboards as well.

                                   

                                  Actually based on Month/Rolling, I wanted the other drop down to update.

                                   

                                  Here I have Filter by Rolling/Month as a filter. This can be applied on multiple worksheets too. To make this work (as per Shin's suggestion), I created one more copy of the data source in the data pane. Added a field called as Blend to blend these data sources.With this, I am trying to calculate current month, previous month and so on. This my calculation.

                                   

                                  However, this is not working as expected. It will be glad if you could tell me the correct approach. Attached is the updated workbook.

                                   

                                  Thanks again and apology for taking your time.

                                   

                                  Regards,

                                  Tushar

                                  • 14. Re: Latest Month, Prior Month, and Previous year same month
                                    Simon Runc

                                    hi Tushar,

                                     

                                    Is there a reason why you can't just use the same parameter for both options? They both require a Year-Month input (is there a reason one needs to be YYYY-MMM and the other YYYYMM?)

                                     

                                    If you can use the same one, then you can use the [Month/Rolling] parameter to choose if is uses a = (for Month) or Between (using <= Month AND >(Month - 24 Months) for Rolling)

                                     

                                    If you do need 2 different styles of input, the only way I know would be to use Joe's Sheet Popping trick

                                     

                                    Sheet Swap and Pop in Tableau on Vimeo

                                     

                                    with the restriction here being that you'd need to use "Fixed Size" dashboard.

                                     

                                    Again, without Dynamic parameters we have no way of amending the parameter list of values.

                                    1 2 3 Previous Next