7 Replies Latest reply on Nov 12, 2012 8:04 AM by Derk Busser

    looking at snapshots of data by date

    Ronak Patel

      I have some what of a complex situation. I have transactions for customers by date but I want to look at them in the business day ends of day, month, quarter, and year. For example the last day in each month would be the sum the user sees at the month level, not each day of the days inside the month summed up. So months Jan, Feb, Mar would show the total for days 1/31/2012, 2/28/2012, 3/30/2012. At the quarter level, q1 would show the total for 3/31/2012, and q2 6/30/2012, etc...

       

      I have a time DM table with the following columns

       

      DATE        |    MONTHEND_DATE | QUARTEREND_DATE | YEAREND_DATE

      yyyymmdd         (Y/N)                              (Y/N)                       (Y/N)

       

      I'm getting confused on how I would set this up so it flows with how the user is navigation through the date hierarchy.

        • 1. Re: looking at snapshots of data by date
          Derk Busser

          Would it be easy to use a parameter for this? E.g. 3 values, Month View, Quarter View, Year View.

           

          Then a calculated field with an if statement:

           

          IF([ViewParameter]=="Month") THEN [MONTHEND_DATE]

          ELSEIF(([ViewParameter]=="Quarter") THEN [QUARTEREND_DATE]

          ELSE THEN [YEAREND_DATE] END

           

          Another solution would be to use the Monthend_date as a basis for the DATE hierarchy and then use subtotals for the Quarter and Years by going to Analysis -> Totals -> Add All Subtotals. Then go to the quarter Dimension and select "Add subtotal"  Like in the screenshot.

           

          I hope this is what you were looking for.

           

          -DerkScreen Shot 2012-11-09 at 7.53.50 AM.png

          • 2. Re: looking at snapshots of data by date
            Ronak Patel

            Almost, but I'm not looking for a subtotoal of the entire quarter. I'm looking for a total of all the transaction on the last DAY of the quarter, or month, or year.

             

            The question being asked in the data would be: "What was the total value of all the accounts on the last DAY of the month, last DAY of the quarter, and last DAY of the year?"

             

            I think where it gets complex is that if I want the user to be able to drill from year down to month, how to show the values of the LAST DAY of those time hierarchies.

            • 3. Re: looking at snapshots of data by date
              Derk Busser

              Oke, then you can use the max function on dates i guess?

              Make the date into a custom Month/Date/Year value, and then take the MAX date. Then just add the same Date to form the hierarchy for year, quarter and month.

               

              2012-11-09_1406.png

              • 4. Re: looking at snapshots of data by date
                Ronak Patel

                ok, so it seems to be working almost. what I saw is that lets say I'm at the q3 level so the last day of q3 is 9/31/2012. If a record has transaction for lets say 8/31/2012 and not for 9/31/2012, it uses 8/31/2012 as the max date for that transaction then so it q3, I get values for 8/31 and 9/31 then but I just want 9/31 values.

                • 5. Re: looking at snapshots of data by date
                  Derk Busser

                  oke, but i thought your data had the quarter end dates? otherwise you would need to add some checks in calculated fields to check if the date is actually the last date in that period, e.g. all last month days and last quarter days.

                  • 6. Re: looking at snapshots of data by date
                    Ronak Patel

                    It does, but it seems like there are issues on the ETL side and the flags aren't correct, is there a way tableau would do a max date by date level? I guess I could also just recreate the table in Excel and blend the sources. Right?

                    • 7. Re: looking at snapshots of data by date
                      Derk Busser

                      Right, you could do that... OR... you can do a check if the date is indeed the last day of the quarter or month by doing a calculation as such:

                       

                      DATEADD('day',-1,DATETRUNC('quarter',DATEADD('month',3,#12-11-2012#)))

                       

                      The DATETRUNC function truncates a date to the first date of the timeperiod that is selected: in this case the quarter. To get the LAST date of a quarter, we need to be sure that the DATE is in the next quarter, then truncate, and then decrease by one day so you get the last day of the period.

                       

                      A simple check to see if it is the last day would then be:

                       

                      IF [date] = DATEADD('day',-1,DATETRUNC('quarter',DATEADD('month',3,#[date]#))) THEN "Last day of the quarter" ELSE "Not the last day of the quarter" END

                       

                      For other periods it would be, month:

                       

                      DATEADD('day',-1,DATETRUNC('month',DATEADD('month',1,#12-11-2012#)))

                       

                      and Year:

                       

                      DATEADD('day',-1,DATETRUNC('year',DATEADD('year',1,#12-11-2012#)))

                       

                      Otherwise you should indeed create a seperate XLS with the correct dates.

                       

                      -Derk