9 Replies Latest reply on Nov 7, 2018 12:53 PM by Deepak Rai

    How to get Month/quarter & year data together ?

    Kahv Jiv

      Hi, I am struggling to get the value of Month & qtr displayed together.I have created a parameter which has all month and qtrs defined.

      I want when the user selects Jan and year as FY2016, profit gets displayed for that particular intersection and when the user selects Q1 and year as FY2016, then value for that intersection gets displayed.

      I guess the issue is with the calc field I am using.


      I have mocked up the data, can anyone help me to fetch proper values ?


      Thanks !

        • 1. Re: How to get Month/quarter & year data together ?
          Michael Hesser

          Hi Kahv;

          Are you trying to get either the month OR the quarter results to display (whichever the user selects), along with the appropriate year?


          Here's my solution, though I suspect someone will be able to find a simpler one.


          I changed your calendar parameter to look like this:


          Those numbers look pretty crazy, don't they? Hang on, it gets stranger.

          I made three new calculations called Month A, Month B, and Month C.

          EDIT: this should read int(mid([Calendar New],3,2))

          These calculations dissect the numbers associated with the Month/Quarter. For Jan we'll get {1,1,1} but for Q1 we'll get {1,2,3}


          I also created a Month of Order calculation and dropped this into the Filters:

          In the Filters, I added some conditions to it:


          As the user moves between Month and Quarter, the totals align themselves properly. Again, I'm uncertain if this is the type of functionality you're after (I also apologize: my attackment is in 10.0 and you may not be able to upload it).


          Wishing you the best! --Michael

          2 of 2 people found this helpful
          • 2. Re: How to get Month/quarter & year data together ?
            Christina Gremore

            Hi Kahv,


            Let's take a look at what your calc is doing. You wrote:


            IF [Calendar] = 'Jan'

            THEN DATEPART('month', [Order Date])+ DATEPART('year', [Order Date])

            ELSEIF [Calendar] = 'Feb'

            THEN DATEPART('month', [Order Date])+ DATEPART('year', [Order Date])



            What is this calc doing?


            It tells Tableau, "For every order, look at the Order Date. Take the integer of the month and add it to the integer of the year." For example, for Joy Smith, who ordered a Table in Indonesia on 5/1/2014, your calculation is returning 5 + 2014 = 2019. As you can see, this number is really quite arbitrary and has no relation to the profit number. When you grab this calculation and sum it as a measure in your visualization, it's returning you the sum of all these outputs across all these numbers.


            Showing both month-level and quarter-level sales from the same parameter would be quite an advanced move - I think for now you need to focus on just getting a calculation that will output the monthly sales correctly with your parameter. To get this calculation, follow these steps:


            1. Create an integer type parameter, with Allowable Values set to "List." In your list, the values should be month parts - basically, type out the numbers 1 through 12 in a list. Then change the display values to the corresponding months - Jan, Feb, Mar, etc. You change these values by double-clicking the value in the 'display as' column. (See 'Integer Calendar' in the attached twbx for an example.)


            2. Now you will create a calculated field that you can put on the filter shelf. It will look like this:


            MONTH([Order Date]) = [Integer Calendar]


            What is this calculation doing? It is a Boolean, so it evaluates only to true or false. For example, when you have the Integer Calendar parameter set to Jan, which as you remember, has a value of 1, it looks at the month of every order date. If the order was placed on 1/24/2014, then MONTH(Order Date) =1, so the calculation returns 'TRUE' for that record. If the order was placed on 3/13/2014, then MONTH(Order Date) = 3, so the calculation would return 'FALSE' for that record, because 3 does not equal 1.


            3. Take the Boolean calc you just created and put it on the filter shelf. Set the filter to keep only 'True' values. Now create your view - in the attached 'Revised' sheet, I put SUM(Sales) on columns and SUM(Profit) on label. As you can see, the visualization updates correctly when you choose a different month. This filter calc also plays well with the YEAR filter you have already on there.


            As I said before, trying to get month-level data AND quarter-level data to calculate correctly from the same parameter and calculation requires a fairly deep understanding of how Tableau works. It would be much simpler to have a cascading display of filters - one that shows year, another that shows quarters, and a third that shows months. You could even do this without parameters at all, just regular quick filters and the "Only relevant values" option. If your org really has its heart set on choosing specific months and quarters from the same drop-down menu, I'd recommend hiring a Tableau developer consultant to attempt this task.

            • 3. Re: How to get Month/quarter & year data together ?
              Kahv Jiv

              Thanks a  lot Chritina for the wonderful explanation..  Thanks a ton again !

              • 4. Re: How to get Month/quarter & year data together ?
                chris guth

                Hi Michael,


                Just read your solution here since I am looking to accomplish the same thing. However, when I select "Q1" from the selection box, the amount displayed equals the sum of Jan + Mar, not Jan + Feb + Mar.




                • 5. Re: How to get Month/quarter & year data together ?
                  Michael Hesser

                  Hi Chris--

                  WOW-- I had to scratch my head when I saw your reply-- I wrote this so long ago I didn't recognize what I wrote!


                  Are you saying that February was not showing up when you run it?


                  If so, try modifying Month B calc so it reads:


                  int(mid([Calendar New],3,2))


                  If you're trying to get it to sum just Jan & March and skip February altogether, let me know


                  If these are helped you, kindly mark them to let me know!

                  1 of 1 people found this helpful
                  • 6. Re: How to get Month/quarter & year data together ?
                    chris guth

                    Hi again Michael,


                    I tweaked the Month B calc field and it looks like it's working now. Awesome solution you provided. Thanks for the help with my problem.



                    • 8. Re: How to get Month/quarter & year data together ?
                      Michael Hesser

                      WOW Deepak Rai ! That's fine praise! Thank you! When I hear something like that (even a simple comment) I take it to heart-- it means a so much.

                      All the best!

                      • 9. Re: How to get Month/quarter & year data together ?
                        Deepak Rai

                        You Really Did Good on That!!