8 Replies Latest reply on Dec 2, 2016 8:09 AM by Larissa James

    Converting YTD data to MTD

    Larissa James

      I've been searching for an easy solution to this problem, but so far have been unsuccessful. I have a data set where the data is reported monthly, but the results are year-to-date. I need to a backwards type calculations to get the monthly results for each month. I know I could do it easily in Excel before importing it, but was hoping there would be an easy way to do it in Tableau since my Excel data file will be very large and will be updating each month. I have tried to use the difference table calculation, but the problem I keep running into is when I switch years it is subtracting January from the previous December, rather than providing just the January result for the current year and starting the difference calculation over. Hoping someone will have an easy solution. I've included a small sample of the data I'm working with.

       

      Thank you!

      Larissa

        • 1. Re: Converting YTD data to MTD
          D Barnetson

          Hi Larissa,

           

          Just to make sure that I've understood your question correctly, you are looking to show the current month to date?

           

          Are you looking to have the graph look something like the image below?

           

           

          Let me know if I'm on the right track.

           

          Don

          • 2. Re: Converting YTD data to MTD
            Larissa James

            Yes, kind of. For each state, I want to see the whole year by month - but the moth results (bars) should represent just that current month's results and not the YTD for the month. For example, the March bar would be just March and not January - March. The way the data comes in, March is representing January - March, so I need to strip out the January and February results from the March number. For February, I'd need to subtract out January, etc. Hope that helps clarify.

            • 3. Re: Converting YTD data to MTD
              sudhakar reddy

              Hello Larissa,

               

              Please see the below screenshot and let me know this is what you are looking for.

              difference from previous.PNG

               

              if this is what your looking for you can achieve this by using difference "Quick Table Calculation"

               

              Regards,

              Sudhakar Reddy

              • 4. Re: Converting YTD data to MTD
                D Barnetson

                Thanks for the clarification.

                 

                I think I have figured it out.

                 

                To start, you will have to create a new pill for January (which I've named 'Sales Mth 1') using the following calculation:

                 

                This will give you the sales for January only...you will see why shortly.

                 

                Now, place 'Sales Mth 1' on to the rows shelf.

                 

                Right click on the pill and select 'dual axis', then right click again and select 'Synchronize Axis'.

                 

                Right click on the 'Sales' pill, on the rows shelf, and select 'Quick Table Calculation', then select 'Difference' (this will calculate the difference from the prior month).

                 

                At this point you may have noticed negative values for January.....

                 

                To remove the false negatives for January, right click on 'State' on the column shelf and select 'Attribute'.

                 

                The picture below is what your graph will look like.

                 

                 

                I have attached a packaged workbook (Tableau v10.1), though I'm not sure if you will be able to open it as it is v10.1.

                 

                Hopefully my explanation is clear enough to help.

                 

                Don

                • 5. Re: Converting YTD data to MTD
                  Larissa James

                  That works, until I add the next year's data - then January of the new year is subtracted from December of the previous year and I end up with a negative number for January. I need a formula to basically say if the month = January then give me the sum of sales else if the month <> January then give me the current month - previous month for sales. I've updated the sample data with additional data for the next year to demonstrate the problem I am having.

                  • 6. Re: Converting YTD data to MTD
                    Larissa James

                    I am not able to open your attached workbook since I don't have 10 yet (GRRRRR)   I did try to mimic your solution; however, I still end up with negative numbers for January. I've re-attached my workbook.

                    • 7. Re: Converting YTD data to MTD
                      D Barnetson

                      Alright, you're almost there.

                       

                      Right click on the 'Sales' pill on the rows shelf. Select 'Compute Using', then click 'Pane (across)'.

                       

                      That removes the negatives.

                       

                       

                      Hope this helps.

                      Don

                      • 8. Re: Converting YTD data to MTD
                        Larissa James

                        YES! That worked!! Thank you so much!