9 Replies Latest reply on Oct 19, 2018 10:29 AM by albert wong

    Creating own dates

    Aleksandar Angelov

      Hi all,

       

      I was wondering if you can help me for a project that I am currently working on...

       

      I have a "Close date" field which is the date that a certain order is completed. On the other hand, I have the amounts of the orders.

       

      Now I want to create a report that shows me how the total amount is fluctuating every single day. However, the problem is that these dates ("Close date") can be at any point of time...in the future, in the past etc. And they are not capturing every single day.

       

      As such my questions is can I create a parameter, set or calculated field in order to have every single day since I haven't column in the database with this values.  When I create today() I have today's date in a field which does the job but only for 1 day   Can I somehow create more dates ?

       

      For example, if I have the following values yesterday:

       

      Closed date | Value

      01-Jan-05 | 10

      05-Sep-16 | 15

      20-Oct-18 | 30

       

      And the following values today 18-Oct-2018:

       

      Closed date | Value

      01-Jan-01 | 100

      05-Oct-16 | 200

      20-Oct-19 | 50

       

      And the following values tomorrow 19-Oct-2018:

       

      01-Jan-01 | 100

      05-Oct-16 | 200

      19-Oct-19 | 30

      20-Oct-19 | 50

       

      Then I want to have

       

       

      17-Oct-2018 | 55

      18-Oct-2018 | 350

      19-Oct-2018 | 380

        • 1. Re: Creating own dates
          Ankit Bansal

          Aleksandar,

           

          You can use closed date only and make it continuous date. it will create a view with from minimum of closing date to max of closing date.

          • 2. Re: Creating own dates
            Aleksandar Angelov

            Yes, but I don't want that. I want the SUM of all CLOSE DATE's every single day

             

            For example if I have the following values yesterday:

             

            Closed date | Value

            01-Jan-05 | 10

            05-Sep-16 | 15

            20-Oct-18 | 30

             

            And the following values today 18-Oct-2018:

             

            Closed date | Value

            01-Jan-01 | 100

            05-Oct-16 | 200

            20-Oct-19 | 50

             

            And the following values tomorrow 19-Oct-2018:

             

            01-Jan-01 | 100

            05-Oct-16 | 200

            19-Oct-19 | 30

            20-Oct-19 | 50

             

            Then I want to have

             

             

            17-Oct-2018 | 55

            18-Oct-2018 | 35019-Oct-2018 | 380

            • 3. Re: Creating own dates
              Mahfooj Khan

              Hi,

               

              I don't think tableau will generate incremental dates the way you want, better you should add one more date field (CURRENT_DATE) in your source which capture current dates based on daily transaction so that you can track daily updates easily.

               

              Mahfooj

              • 4. Re: Creating own dates
                Vinnie Ahuja

                Try using a second data source that is a "calendar".  This could be an Excel file that just lists out all the dates in your range, or could be a DBMS source such as SYSDATE in Oracle or sys_calendar.calendar in Teradata.  Do a full outer join to this data source using your Closed Date and then use the date from the calendar source as your filter.

                • 5. Re: Creating own dates
                  albert wong

                  To add to what Vinne said, I would try "Scaffold Dates".

                   

                  For example i created an Excel workbook with manually entered dates.

                  (for example in the first cell, i entered "1/1/2018" and dragged it down for a whole year.

                   

                  Right Join the scaffold to your SQL query and select "Create Join" drop down and enter DATE(Closed Date) and DATE(DateFromExcel).

                   

                  Bring your Excel Date to Rows and COUNT(Closed_Date) to columns.

                  • 6. Re: Creating own dates
                    Aleksandar Angelov

                    Thanks! I followed your guidance

                     

                    Filter: Close Date

                     

                    Rows: SUM(Amount)

                    Columns: DAY(ExcelFormat)

                     

                    But it gives me the same values if I use

                     

                    Row: SUM(Amount)

                    Columns: Close Date

                    • 7. Re: Creating own dates
                      albert wong

                      Can you recreate what you're trying to accomplish with an extract using the sample store data and upload?

                       

                      It will be much easier to come up with a calculation that works for the issue you're describing

                      • 8. Re: Creating own dates
                        Aleksandar Angelov

                        Guys I created the dates and now I have Null's where the value of Close Date is not populated - which is OK I guess. However, when I made a Running Totals for the formula and filter it per particular date then my cart looks like

                        Screen Shot 2018-10-19 at 17.10.40.png

                         

                        However, I would like my first day to be the sum of all previous ones...just wonder how I can do that?

                         

                        Regards,

                        Alex

                        • 9. Re: Creating own dates
                          albert wong

                          Hi Aleksndar,

                           

                          I've attached a sample from the superstore.

                           

                          I have (2) sheets : Individual Totals and Grand Total

                           

                          The Individual Totals shows the sum of sales per day.

                           

                          The Grand Total is just that. Sum(sales).

                           

                          I then created a dashboard and brought the 2 sheets together.

                           

                          From the Individual Totals Sheet, I brought out the Filter for "Order Date".

                           

                          Now you can select the Range of Dates to view the Grand total in the range you want.

                           

                          Hope this helps.