1 2 Previous Next 15 Replies Latest reply on Aug 12, 2016 12:57 PM by Tom W

    date columns independent of data

    lee keenan

      Hi,

       

      I need a crosstab with dates in the columns that I can then use for table calculations. For example I need the last 12 months, from which I can use for calculations such as : where column is Jan 2015 sum work hours for people with start dates >= jan 2015. Obviously when I use start date for the column I only get the work hours for those who started in Jan 2015.

       

      I have tried using an excel file just containing all dates but in order to use this I need to make a relationship, however the only data I can use to form the relationship is the start date which puts me back to square one.

       

      Any ideas on how I can do this?

       

      Many thanks

      Lee

        • 1. Re: date columns independent of data
          Cyril Belmehdi

          I have a solution for you.

           

          If you use a date filter, all your window calculation will be based on your filtered data only.

           

          If you want to display a month but keep the calculation on all of them, you need to create a display attribute :

           

          Display date :

          lookup(attr(DATENAME('year',[REAL_DATE]) +"-"+ right("0" + STR(DATEPART('month',[REAL_DATE])),2)),0)

           

          This formula is for a 2015-01 display, change it if you want something else.

           

          Now your real date filter is not a display filter anymore, it's a month of calculation filter

           

          Kind regards

           

          Cyril

          • 2. Re: date columns independent of data
            Tom W

            I think it would be a better approach if you upload a Tableau Packaged Workbook containing the structure of the data you're basing your calculations off, along with an explanation of what you're trying to achieve.

            • 3. Re: date columns independent of data
              lee keenan

              Hi,

               

              Attached is a workbook with an example data source.

               

              Ideally I am trying to sum the work hours for those where:

              start date <= the end of the 'month' and leave date is >= the start of the 'month'

              (basically it is a count of employee work hours per month of employees who started at any time in the month or before, but keeps employees in the count if they left in the same month)

               

              This 'month' is independent of the data source, as I cant use the start date as this will only include employees who started in this month, and not all the months proceeding.

               

              For example,

               

              for January the work hours sum would be 760

               

              Many thanks

              • 4. Re: date columns independent of data
                Tom W

                How is the sum for January 760?

                The two records with an 'En Date' have an 'En Date' > 1/1/2014, so why wouldn't they be counted?

                • 5. Re: date columns independent of data
                  lee keenan

                  sorry tom, yes you are correct, total should be 840

                  • 6. Re: date columns independent of data
                    . Beley

                    I have this exact same issue in one of my dashboards and have not been able to find a solution. Any help?

                    • 7. Re: date columns independent of data
                      Tom W

                      Beley,

                      Please post a Tableau Packaged Workbook including sample data and outline some details on your problem.

                      • 8. Re: date columns independent of data
                        . Beley

                        Thanks Tom! My data is sensitive so versus taking the time to scrub it, my issue is very similar to Lee's so the dashboard posted entitled "Help" will do the trick for sample data.

                         

                        Basically, trying to see a trending of the sum of work hours where the st date <= "arbitrary date" and en date > "arbitrary date", where "arbitrary date" is any n date that falls within a selected timeframe.

                         

                        I have tried every work around that I can think of but ultimately it seems like you need to create a completely independent date to use on your columns shelf. However, I don't know how to do this.

                         

                        Thanks for the help!

                        • 9. Re: date columns independent of data
                          Tom W

                          Drag Start Date to the columns, create a parameter for the date then create a calculated field to implement the filter you've outlined like SUM(IF [St Date]<= [MyDate] and [En Date]> [St Date] then [Work Hours] end)

                          • 10. Re: date columns independent of data
                            . Beley

                            But its not En Date > St Date, I need En Date > MyDate. However, I don't think you can make a single parameter that can act as range.

                             

                            The calculation I need is:

                             

                            RUNNING_SUM(Sum(If [St Date] <= [Arb Date] and [En Date] > [Arb Date]

                            Then [Work Hours]

                            ELSE 0

                            End ))

                             

                            But I don't know what to use as the Arb Date or how to create it so that it isn't solely based on the st date or the en date.

                             

                            Thanks for the help!

                            • 11. Re: date columns independent of data
                              Tom W

                              The parameter doesn't need to act as a range, it's a single point in time.

                              If you need it to act as a range create a start date parameter and an end date parameter.

                              • 12. Re: date columns independent of data
                                . Beley

                                I need to have a dynamic date range, so that some one can choose a start date and an end date and see the trending in between those days. I know how to do this with parameters. But for the calculation, I can't have it based off of the selected start and end dates because it has to be calculated for each of the individual days within the range.

                                 

                                Ex.

                                Start Date = 7/1/16

                                End Date = 7/31/16

                                 

                                Calc on 7/1/16 =

                                RUNNING_SUM(Sum(If [St Date] <= [Arb Date = 7/1/16] and [En Date] > [Arb Date = 7/1/16]

                                Then [Work Hours]

                                ELSE 0

                                End ))

                                Calc on 7/2/16 =

                                RUNNING_SUM(Sum(If [St Date] <= [Arb Date = 7/2/16] and [En Date] > [Arb Date = 7/2/16]

                                Then [Work Hours]

                                ELSE 0

                                End ))

                                .

                                .

                                .

                                Calc on 7/31/16 =

                                RUNNING_SUM(Sum(If [St Date] <= [Arb Date = 7/31/16] and [En Date] > [Arb Date = 7/31/16]

                                Then [Work Hours]

                                ELSE 0

                                End ))

                                 

                                Thank you!

                                • 13. Re: date columns independent of data
                                  Tom W

                                  "I can't have it based off of the selected start and end dates because it has to be calculated for each of the individual days within the range."

                                   

                                  I don't know what you mean by this. The calculations you've outlined look fine - why can't you just replace the 'arb date''s you're putting in there with a parameter for the start date and a parameter for the end date? Perhaps they need to be dynamic relative to today? Then use the NOW() function. It's too hard for me to say because you haven't provided a sample with data which supports your scenario or any expected results. Thus, I don't know how to get from the calc you've provided to 'the right calc'.

                                   

                                  You need to put together a sample with your own data to support your scenario and put it into a Tableau Packaged Workbook. With that, you need to share some expected results. I'd also recommend you start your own thread so we can deal with your issue there. Please tag me in your new thread and I'll glady pickup where we've left off once I've got a good sample and some expected results to work off.

                                  • 14. Re: date columns independent of data
                                    . Beley

                                    How do I attach a workbook? I don't see an attachment option.

                                     

                                    I could replace the arb date with the parameter dates, but those are static. So then the calculation is the same for every day within the time period because the start and end date parameters are not dynamic. I need the calc to be calculated for each day within the selected time range not just for the date selected in the parameter for start and end.

                                     

                                    Additionally, if you create a date field based on the parameters, you have to chose to either use the st date or the en date for it. In which case, when you put it on columns and put the calculated field for hours on the row, all values are null because the calc is based off of both en date and st date and not just one exclusively.

                                     

                                    If you tell me how to attached a workbook, I'll send it along.

                                    1 2 Previous Next