8 Replies Latest reply on Jan 22, 2013 9:44 AM by Michel Khennafi

    Different date Calculations

    Michel Khennafi

      Good day to all:

      I would like to create a table where for any given product X (rows) I have several columns with the sales number generated

      - The current week (Column 1)

      - The current Month (Column 2)

      - The current Quarter (Column 3)

      - Year to date (Column 4)

      - Last Year for the same year to date (Column 5)

      - YTD - (YTD-1) % variation (increase or loss in %)

      I have tried many formulas but never gotten the appropriate results...

      Has someone created the same type of tableau? Has anyone some examples to share?

       

      See my attached example:

       

      I appreciate your patience and support

       

      Thanks to All,

       

      MK

        • 1. Re: Different date Calculations
          Shawn Wallwork

          Michel, in the future it would be helpful if you included a full set data, not just a single row. I filled out your data arbitrarily:

           

          ProductCurrentWeekCurrentMonthCurrentQuarterYTDYTD-1
          Product A             150           1,000              3,000             18,000               17,485
          Product B             160           1,500              3,500             25,000               45,000
          Product C             170           2,000              4,000             32,000               72,515
          Product D             180           2,500              4,500             39,000             100,030

           

          From here you can create a calculated field to work out the variation:

           

          (SUM([YTD])-SUM([YTD-1]))/SUM([YTD-1])

           

          And then format it as a percentage. (See attached.)

           

          But having said all this, I suspect that the attached worksheet isn't really how your data is actually shaped and the file you attached was the desired out come. If this is the case please post a small sample of your actual data.

           

          --Shawn

          • 2. Re: Different date Calculations
            Michel Khennafi

            Good morning Shawn and thanks a lot for looking into the issue.

             

            I need help in figuring out the sum for the dates... The final calculation (the % YTD vs. YTD-1 is pretty easy).

             

            I would like to be able to create calculated fields that handle the sum for different periods of time (this week, last month...)

             

            What is the calculation formula to have a column that displays the current week totals? the current month totals, the current quarter totals...? This is where my headache is - if I may say so -

            • 3. Re: Different date Calculations
              Shawn Wallwork

              Michel, I see what you mean. So I'm pinging Jonathan, the text table king.

               

              --Shawn

              • 4. Re: Different date Calculations
                Jonathan Drummey

                @Michel - I can't give you an answer without seeing your data, because the aggregations will depend on the format of your data. Please post a packaged workbook with a useful number of rows of sample data. Alternatively, you could point to some fields in the Superstore Sales data set that comes with Tableau.

                 

                @Shawn - I wouldn't call myself the "text table king," more a "slightly deranged text table experimenter."

                 

                Jonathan

                • 5. Re: Different date Calculations
                  Shawn Wallwork

                  New title noted!

                   

                  --Shawn

                  • 6. Re: Different date Calculations
                    Michel Khennafi

                    Good morning Jonathan and Shawn ... looks like we are old pals here ;-)

                     

                    As per your suggestion, I am attaching sample data using a Packaged workbook.

                     

                    The DLRCD code is our customer number... In this sample, the desired outcome is a table that has each customer isn a row and a set of columns created using calculated fields where each column corresponds to a period of time.

                     

                    I would like to regroup the different number fields (let's say for instance LINES) and determine how many lines where shipped

                    - Column 1: THISWEEK (using the Weeknum of the date the tableau is opened)

                    - Column 2: LASTWEEK (using week of current date - 1, I suppose)

                    - Column 3, THISMONTH (sum of all the lines done on the current month, using today's date as the way to determine the month number...)

                    - ...

                     

                    I imagine you see the jist of it...

                     

                    Thanks a bunch for showing me a couple of examples and I will use your suggestion to extend to other date time calculation.

                    • 7. Re: Different date Calculations
                      Jonathan Drummey

                      The easiest way for me to do this is to build some row-level calculations and surround them with a SUM(), see the attached for details.

                      1 of 1 people found this helpful
                      • 8. Re: Different date Calculations
                        Michel Khennafi

                        Thanks so much Jonathan:

                         

                        This one direction will give me opportunities to explore... I checked the calculations you have made (using Anchor Date to have a milestone / starting point)... I will duplicate your method no later than this afternoon / tomorrow and come back to you.

                         

                        As usual you deliver!

                         

                        Michel