12 Replies Latest reply on Jan 13, 2017 7:14 AM by Shinichiro Murakami

    Show Current Month v Previous Month

    Helen Rowson

      Hi,  I'm new to Tableau with limited SQL knowledge.  I'm trying to get my report to show just the current and previous month performance.  When the 'select month' parameter is set to e.g. September 2015, the report pulls through performance for that month and the previous month based on the 'Month' dimension.  I only want to show the relevant months.  See screenshot attached.  I think it should be something like if select month parameter is in current month calculation then '1', then I can filter on '1'.  I have a feeling this is a really easy calculation when you know what you're doing!

        • 1. Re: Show Current Month v Previous Month
          Shinichiro Murakami

          Hi, Helen

           

          Your approach is good to meet the request.

           

          Create parameters and then you can filter the header itself.

           

          [Filter]

          if [Order Date] >= dateadd('month',-1,[Select Month]) and [Order Date] <dateadd('month',1,[Select Month])

          then [Order Date] end

           

          filter out null from this filter.

           

           

           

          If you need to show the all the header and want to show the value only on this month and last month.

          Formula canbe below.

           

          [Sales Value this month and last month]

          if [Order Date] >= dateadd('month',-1,[Select Month]) and [Order Date] <dateadd('month',1,[Select Month])

          then [Sales] end

           

          Thanks,

          Shin

          3 of 3 people found this helpful
          • 2. Re: Show Current Month v Previous Month
            Lisa Li

            Hey Helen,

             

            Similar to Shin's solution, another way to achieve this is to create a calculated field that shows the Sales for the desired months and filtering to show only those values.

            Month.PNG

            filter.PNG

             

            -Lisa

            CoEnterprise | Blog

            • 3. Re: Show Current Month v Previous Month
              Helen Rowson

              Thank you so much! This worked! I've spent ages trying to figure it out but I knew it would be easy for someone with more experience.  Getting there a step at a time.  Thank you again!

              • 4. Re: Show Current Month v Previous Month
                Helen Rowson

                Thank you so much Lisa.  I tried Shin's calculation and it worked! Very happy.  Will save any suggestions in the bank though for future reference.  Thank you for responding to my post. Best wishes.Helen

                • 5. Re: Show Current Month v Previous Month
                  Kerry Evert

                  Hi Shin,
                  I found this very useful and it has worked perfectly for my report.

                   

                  I wonder if you would be able to help me to expand this to allow me to show the same month in the previous year in the same table?

                   

                  So I've currently got a table which has the selected month (done in the way you recommended above, now I want to add in the same month from last year, so that I will have:

                                      Sep 2016     Aug 2016     Sep 2015

                  Metric 1          x                    x                    ?

                  Metric 2          x                    x                    ?

                  Metric 2          x                    x                    ?

                   

                  I have tried adding in the following into the filter calculation (the bold part):

                  if [month]>=DATEADD('month',-1,[Select Month]) and  [month]<DATEADD('month',1,[Select Month])

                  and  [month]>=DATEADD('year',-1,[Select Month]) then [month]

                  END

                   

                  But that still only gives me 2 columns:

                   

                  Thanks in advance

                  Kerry

                  • 6. Re: Show Current Month v Previous Month
                    Shinichiro Murakami

                    Here you go.

                     

                    [Filter 2]

                    if ([Order Date] >= dateadd('month',-1,[Select Month])

                        and [Order Date] <dateadd('month',1,[Select Month]) )

                    or ([Order Date] >= dateadd('month',-12,[Select Month])

                        and [Order Date] <dateadd('month',-11,[Select Month]) )

                    then [Order Date] end

                     

                     

                    Thanks,

                    Shin

                    1 of 1 people found this helpful
                    • 7. Re: Show Current Month v Previous Month
                      Kerry Evert

                      Wonderful!

                      You are a super star!

                       

                      Annoyingly, I updated my calculated field, but it showed all 12 months in the table. But after deleting and starting that calculated field again, it worked! How odd!

                       

                      I really appreciate your assistance with this!

                      • 8. Re: Show Current Month v Previous Month
                        Shinichiro Murakami

                        You are very welcome.

                        Jut one more thing.  Could you mark my answer as correct?

                        ^

                        |

                        |

                        Sorry, it's for Helen....

                         

                         

                        Thanks,

                        Shin

                        • 9. Re: Show Current Month v Previous Month
                          Kerry Evert

                          Hi Shin,

                          I have a further complication with this same calculation. You see I need to provide the difference between this month of 2017 and this month of 2016 (i.e. same month previous year).

                           

                          I've done some playing around with the calculation and I can't get it to give me just that. Using the calculation above:

                          if ([Order Date] >= dateadd('month',-1,[Select Month])

                              and [Order Date] <dateadd('month',1,[Select Month]) )

                          or ([Order Date] >= dateadd('month',-12,[Select Month])

                              and [Order Date] <dateadd('month',-11,[Select Month]) )

                          then [Order Date] end

                           

                          Do you know how I could show just Jan 2017 and Jan 2016 (for example)

                           

                          Thanks again

                          Kerry

                          • 10. Re: Show Current Month v Previous Month
                            Shinichiro Murakami

                            Kerry,

                             

                            Is this what you want?

                             

                            [Diff from the First]

                            if last()=0 then ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), FIRST()) end

                             

                            [Diff from Previous]

                            if last()=0 then ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1) end

                             

                             

                             

                            Thanks,

                            Shin

                            1 of 1 people found this helpful
                            • 11. Re: Show Current Month v Previous Month
                              Kerry Evert

                              Thanks Shin, that is one way of doing it, and it would work, but it would get messy as I have quite a few metrics in a table.

                               

                              I have a parameter where the user can select a month, then the table will show the previous month, that is no problem. But I want to have another table (which will be totally separate) which will show the same month of last year. So I'm just trying to set up a dimension to take the selected month in the parameter and then give back that month/year and the same month for the previous year.

                               

                              What I have managed to do is get the month in question (so if I select October 2016, October 2015 shows in my table), but I can't seem to get October 2016 in again?

                               

                              • 12. Re: Show Current Month v Previous Month
                                Shinichiro Murakami

                                Kerry,

                                 

                                This post is done thing, and would you mind to make another thread.

                                Sounds like the conversation comes little bit far from original topic.

                                 

                                Thanks,

                                Shin