3 Replies Latest reply on Feb 4, 2012 12:13 PM by Alex Kerin

    Grouping items



      Please give me some ideas on how this can be down:

      I have a dataset, in which each record has got an ID, an approved date  and a record_created_date.

      All records have got the record_created_date but all of them dont have the "approval_date".

      I would like to see in my worksheet, how many records were created in a particular month and year "say MARCH 2011" and the total of how many got approved in that month and year(those that got approved in say March2011, might not have been created on March2011, they might be created anytime in the system), I just want the total of that.

      I took 2 approaches to this problem but neither of them serves my purpose:

      When I pulled the approval_date in the columns and "count of my records" as rows, and drill-ed down in the month level, i could see the counts of approved records broken down by approval_date as month and year. But when I want to plot the total records that were in my database for a particular month and year, I am able to see just the number of records that have approval_dates, if they do not have a"approval_dates", it is NULL in database. Tableau is eliminating all the records that have NULL "approval_dates". So I can see only 8 records for March 2011 as having approval_dates and only 8 is showing as total records for "March 2011", as the rest of the records which do not have "approval_dates" in other words did not get approved is thrown away by Tableau.


      So the second approach, I took was to drag the record_creation_date in columns and then drilling down to see how many records were created for "March2011" there I can see that 3000 records got created for March 2011 which is correct, but now I if I want to see how many got approved, tableau is  just showing  out of these 3000 records, how many got approved, those approval dates  are anytime after March 2011 and including March 2011.But what I want to see is how many got approved in March, (they may have come in at anytime in the system, even before March2011 and after March2011).


      So summing up, I need to know how many records got approved in just March and how many records got created March, these 2 sets are independent of each other.

      Any help will be appreciated in this regard.



        • 1. Re: Grouping items
          Alex Kerin

          Can you mock up some data that matches your current structure and the expected result?

          • 2. Re: Grouping items

            Table structure:

            ID Record_creation_Date  Approval_Dates

            1  2011-03-03                          2011-10-01

            2 2011-03-03                            NULL

            3 2011-03-03                           2011-03-05

            4 2011-03-03                          2011-11-11

            5 2011-02-01                          2011-03-07

            6 2011-02-02                          2011-03-10


            In my first approach:I put the approval dates in the columns, the single NULL record get eliminated and all statistics are done in 5 records instead of 6. so my approved records in this case shows up as 3 for March 2011, which is right, but the number of records that came in on March 2011 shows up as 3 also, which is wrong, it should be 4.


            In my second approach: I put record_creation_date as my columns, then the number of records that were created for March 2011 come up as 4 which is correct, but the number of approved records for March 2011 come up as 1, which is wrong it should be 3.

            • 3. Re: Grouping items
              Alex Kerin

              Nope, still can't work out what you want as an end result.


              Is it:

              Date                Created              Approved

              March 2011     4                         3

              February         2                         0

              November       0                         1