9 Replies Latest reply on Dec 24, 2015 10:58 AM by Shinichiro Murakami

    How to get current selected month and previous month

    lakshminarayana.m

      Hi,

      I'm trying to figure out how to show selected month data and previous month data and then calculate the difference in months data and need to color code it to green is the difference is + and to red if it is -.

      Please see the attached file for example.

      Appreciate your quick response.

       

       

      Thanks much,

        • 1. Re: How to get current selected month and previous month
          Shinichiro Murakami

          Hi , lakshminarayana

           

          As long as I know, this is the request which is quite easy with excel, but very troublesome in Tableau to get EXACT same result.

          I only show the approach which can bring "similar" result.

          The biggest missing part in this solution is that you will get "colored number" not only %, but also monthly value.

          Quite a long step, but still relatively easier than chasing exact same view....

          At the first time, these steps seems complicated, but once you leaned,  easy to replicate it for sure..

           

          • Create Parameter to select Month [Month_Select]
            • Create parameter (Data type "date")
            • Show Parameter Control
          • Create calculated field
            • [Month(prev)]

          dateadd ('month',-1,[Month Select])

            • [Month-param]

          if year([Month])=year([Month Select]) and month([Month])=month([Month Select])
          then "Current Month"
          elseif year([Month])=year([Month(Prev)]) and month([Month])=month([Month(Prev)])
          then "Previous Month"
          else "OUT"
          END

           

          • Put [Month-param] in the filter and exclude "OUT"
          • Put category in Row shelf
          • Put "Sales" in "text" in marks card
          • Put Month in the Column shelf and change its format to Month/Year
          • Put one more "Sales" pill into "detail" in marks card (not in text)
          • Add table calculation
          • Put Table calculation Pill on top of "table"
          • Put "Measure Names" in front of "MY(Month)" in column shelf.
          • Replace Sum(Sales) and Sum(Sales) with table calc
          • With Pushing "Ctrl" key, drag Sum (Sales) Table Calc pill into "Color"
          • Edit Color and select "Red-Green**"
          • Set Stepped color =2 and go Advanced
          • Then you get this table.....  (This is easier approach by the way.......)
          • I give up today to explain another approach......But attached file includes closer view as well.... (version 9.0)

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: How to get current selected month and previous month
            lakshminarayana.m

            Seems like this is what I need.Thank you Shin for taking time in responding to my request. I'll try this out and will get back.

             

            Thanks again.

            • 3. Re: How to get current selected month and previous month
              Shinichiro Murakami

              Sounds good,

               

              Let me know if you still have problem.

               

              Shin

              1 of 1 people found this helpful
              • 4. Re: How to get current selected month and previous month
                lakshminarayana.m

                Thanks Shin. It worked, but is there a way to do this everything in one worksheet rather in multiple worksheets and attaching it next to one another.

                Just curious to know if the below example is possible to do in tableau in one column.

                 

                  

                3.6%↓ Red when there is decrease in value when compared to previous month
                2%↑
                Green when there is increase in value when compared to previous month

                 

                Thanks much.

                Have a good day.

                • 5. Re: How to get current selected month and previous month
                  Shinichiro Murakami

                  I will work on this late today.

                  Not straight way, but may be there is some method.

                   

                  Shin

                  • 6. Re: How to get current selected month and previous month
                    Shinichiro Murakami

                    This is my best at this point.

                    Still several further steps needed to achieve your pretty simple request. : )

                    Be patient to follow the quite complicated process.....

                     

                    This look like table, but the fact is combination of Bar graph.

                    The numbers are shown as reference value for charts.

                    The difficulty in table view was that I cannot change only one data field in simple table.

                    ie, When I tried color the "difference", Current month and Previous month also become same color in simple table.

                     

                    Now, with creating all data field independently with bar graph with transparent color.

                    You can only see reference value on top of transparent bar chart on the field of current month and previous month.

                    Because Sales cannot be negative, I set minimum value = 0 and maximum value = 1 for axis to align the reference value to the top of bar chart (right side of the table).

                    I aligned reference number to the right of bar chart, it is always ceiling at "1".

                    Then, to show the header on the top, not bottom, I needed to add dual axis only for the purpose of showing header.

                    Then, remove the "text" from bottom header. (Don't hide the header, because bottom is primary header and hiding header resulted in hiding both bottom and top header)

                    Then click none of tick marks,

                     

                    For %, because this number range is from -1 to plus 1, I put axis range from 0 to 0, to show reference number on the center always.

                    And I could put Text Chart instead of bar chart for Difference Field.  If I put bar chart, very small area of color appeared., then align the text to center.

                     

                    Finally, change the Difference fields's text color by setting 2 step red or green with putting zero as center.

                     

                     

                    Thanks,

                    Shin

                    • 7. Re: How to get current selected month and previous month
                      Shinichiro Murakami

                      Lakshminarayana

                       

                      I wonder if you got satisfied with different solution, and want to know your/your colleague's feedback.

                      Because this type of pretty easy request is sometimes very complicated through Tableau and I want to know the any feedback. 

                      If you have time, could you take a look?

                       

                      Thanks,

                      Shin

                      • 8. Re: How to get current selected month and previous month
                        lakshminarayana.m

                        Hi Shin,

                        So sorry..I wanted to reply back to you but was busy with few stuff..Yeah your logic helped me and I'm very much satisfied with it. If i come across any other solution I'll share that with you.

                         

                        Merry Christmas and Happy Holidays.

                         

                         

                        Thanks again.

                        • 9. Re: How to get current selected month and previous month
                          Shinichiro Murakami

                          Hi, Lakshminarayana

                           

                          Thank you for the feedback.

                          Merry Christmas and Happy Holidays to you, too.

                           

                          Shin