3 Replies Latest reply on Oct 13, 2018 7:41 AM by ShivaRam Chennapragada

    Multiple Dates - Blending 3 Dates Into One

    James Julius

      Hello all -

       

      I have a dataset that has three separate date dimensions for date worked, date billed, and date paid.

       

      I also have three measures - amount worked, amount billed, and amount paid.

       

      All of this information - the three measures and the three dimensions - is listed in one single row in the dataset.

       

      What I'm trying to do is represent by week the sum total of 'amount worked', 'amount billed', and 'amount paid' displayed as measure names in a table, the column is a discrete date displayed as week.  The column week would represent the column week that is for date worked, date billed, or date paid.

       

      The output would look something like this:

       

      I've considered blending data and joining to a singular date table with all of the dates but I can't seem to figure those out with three dates.

        • 1. Re: Multiple Dates - Blending 3 Dates Into One
          ShivaRam Chennapragada

          James- What are these datasets? Excel spreadsheets? Database tables? Either case you should be able to JOIN them using a common field (eg: EmployeeID) right? Are dates the only dimensions that you have?

          • 2. Re: Multiple Dates - Blending 3 Dates Into One
            James Julius

            Hi there -

             

            This is a mysql database, sorry I should have included that info.  Each row has the EmployeeID already in it.  There are more elements in the dataset than just those 7, but for all intents and purposes those are the only ones I'm using and I can filter the other ones out if need be.

             

            So I have four dimensions - EmployeeID, DateWorked, DateBilled, DatePaid

            I have three measures - AmountWorked, AmountBilled, AmountPaid

            • 3. Re: Multiple Dates - Blending 3 Dates Into One
              ShivaRam Chennapragada

              Okay, this is something I'd do- I'd code this up in SQL and flatten (de-normalize) this out in the database. JOIN those 3 tables ON EmployeeID, with whatever columns you'd like. Then in Tableau, using it's natural date aggregation, you can just roll this up into weeks. So, for example my week ending is a Sunday, which makes my week start Monday. From date properties I would make this change, then create a calc field 'Week End Date' - which rolls days into 7 day buckets. So I'd have Week End Dates - 10/14, 10/07, 09/30 and so on. You can create this using 'DateWorked'.

              How Can I Create a Week by Week filter

               

              For these dates, I'd have amounts assigned.

               

              If you don't have permissions to create a table in database, then JOIN these in Tableau and let Tableau take care of aggregation.

               

              If nothing works, please mock some data and attach, we'd like to see how your data is structured.

               

              Thanks,

              Shiva.