Grouping Dense Rank into Weeks

DMJerry
DMJerry Member
in Magic ETL

Hello,

 

I am trying to build a product lifecycle comparison using first invoiced date as day 1 and then consildated all of those into multiples of 7, to get sequential weeks.  For instance; day 1-7 would be 1, 8-14 would 2 and so forth.  Mind you, I am using dense rank.

 

Any help would be amazing.

Comments

  • MarkSnodgrass
    MarkSnodgrass Coach

    My first thought would be to use the Date Operations and use the Week of Year function to derive the week number from the invoice date and build your dense rank off of that. However, it sounds like you want to count 7 days from the first invoice date regardless of the day of the week. 

    To get what you want is going to require a lot of steps in the ETL, but you would do a group by to get the min invoice date and then join it back to your main dataset with a full join so that it adds the min date as another column to your dataset. You could then do a date operation tile to get the difference between that min invoice date and the invoice date in each row. You would then use the calculator tile divide by 7 and then another calculator tile and use the ceiling function to round it up to a whole number. 

    I think this should get you what you are going for. 

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Unknown
    Unknown

    Yeah, it's lifecycle so using the date operator was where i hit a wall thinking that.

     

    I like your idea a lot and makes a ton of sense.  Let me try it and see how long the ETL is.  

     

    I'm also trying about doing is creating a google sheet to make rank 1(min date) be 1/1/2019 and then rank 365 being 12/31/2019 and so forth.  Since I'm looking at product that doesn't have much more than a 1 year shelflife, it should work for now. 

     

    Thanks again and I'll try both and let you know.

     

Categories