12 Replies Latest reply on Feb 9, 2017 2:40 PM by Matthew Peterson

    MattPetersonDec29(Tableau10).twbx

    Matthew Peterson

      Good evening.

       

      I have a Tableau question, related to a large SAS data set.

       

      I’ve joined 19 data sets into one using the ‘nodupkey’ in SAS. This is so that I “only” have around 7 million records instead of 80 million.

       

      I’ve also embedded the following code which assigns the rolling count for the number of consecutive months in which the ID field has been assigned to each Group (in layman’s terms how many consecutive months you’ve been attributed to your Group). This may change if you’re no longer attributed under our company or if you’re moving back and forth between Groups and as of a certain month you’ve now been assigned to Group B (your new Group) more than Group A (your previous).

       

      Proc SQL ;

           Create table draft as

      select *

      ,     count (ID) as count

      From work.JOININGOFALLTABLES

      Group by ID, GROUP

           ;

      Quit ;

       

      Now my question is how do I push this number back n-1 months?

       

      Example: if I’ve been assigned to Group A for all of the past 19 months then I want to have a 1 under EACH of the 19 month fields (not a 19 under current month like I currently have).

       

      Also, here is an example of two IDs that have been attributed to different Groups and the totals are right but I don’t want there to be any blanks under any of the date fields (I’ll prob throw in “.”) and I want there to be 1’s, not totals. Ex: a 1 under 201406, 201409 and 201412 for ID 1000001, Group 003054202.  And… a 1 under 201403, 201406, 201409, 201412, 201503 for ID 10000009, Group 002894333.

       

       

       

      I understand the issue in that I only assigned metrics in SAS to the last month but I figure there has to be a way in Tableau to roll back n-1 months.

       

      Thanks,

      Matt