3 Replies Latest reply on Aug 13, 2012 1:29 PM by Mark Holtz

    How to show the full year number as well as discreet month number together

    Ajit Kumar

      HI

      I have date in my data. I have data from 2010.

       

      I am creating a table where I want to show 2010 Total year number, 2011 Total year number and 2012 latest month number in single table.

       

      How to achieve this target?

        • 1. Re: How to show the full year number as well as discreet month number together
          Tracy Rodgers

          Hi Ajit,

           

          I'm not sure if this is what you're after, but by placing the YEAR([date field]) on the view and the measure will show the totals for each year--including all the value to the latest month's data for 2012.

           

          -Tracy

          • 2. Re: How to show the full year number as well as discreet month number together
            Ajit Kumar

            Hi Tracy

             

            Thanks.

             

            I am getting 2010 and 2011 total number but in the same table How will I get 2012 Jan number?

             

            I have attached a Tableau file for refrence

             

            Thanks

             

            Ajit

            • 3. Re: How to show the full year number as well as discreet month number together
              Mark Holtz

              Sounds like you are wanting to do a sort of "collapsed/expanded" hybrid grouping, where you get "2010 total", "2011 total", "2012 up-to-prior-month total" and "Most recent month in 2012" total.

               

              Not sure if you want to always have your most recent be a "most recent full month" or partial. i.e., today is 8/13, so the "Most Recent Full Month" would be July, 7/1/2012 -- 7/31/2012. 

               

              You may want that Last Full month to be your cutoff point, or you may be trying to get the current Month-to-Date (8/1/2012--8/13/2012.)

              I'm am assuming you want the latter of just guessing you want to break off the most recent incomplete month as the "separated" group, but it can be tweaked to handle the other scenario too:

               

              IF DATEPART('year',DateField) < DATEPART('year',TODAY())

              THEN STR(DATEPART('year',DateField))

              ELSEIF DATEPART('month',DateField) < DATEPART('month',TODAY())

              THEN 'This Year to '+STR( DATEADD('month',DATEDIFF('month',#1/1/1900#,TODAY())-1,#1/1/1900#) )

              ELSE 'Since '+STR( DATEADD('month',DATEDIFF('month',#1/1/1900#,TODAY())-1,#1/1/1900#) )

              END

              //STR( DATEADD('month',DATEDIFF('month',#1/1/1900#,TODAY())-1,#1/1/1900#) )

              // provides the first day of the month prior to the current month (change -1 to tweak)

               

              Then, you can add this dimension to the shelf where your date values are and this should split into year columns for the Prior Years, a Current Year Column that goes up to the "cutoff point" and into 1 last column that is "after the cutoff."

               

               

              Additionally, you could modify it to break each month of the current year into its own column.

               

              I'm attaching your workbook with my examples.