Days Since Last Instance

angela2018
angela2018
in Archive

I need to build a beast mode that gives me the days since the last time a sales rep transacted. My dataset includes several transactions by the same rep but I only want one line per sales rep and however many days it has been since their most recent sale. Does anyone have experience with this?

 

--I've built a beast mode to give me the date difference for today vs. the date of sale. I just need it to ONLY return the most recent sale data rather than one line for each of the rep's sales. 

Comments

  • Chips
    Chips Domo Employee

    Could you use a "Max" aggregation on the date of sale?

     

    DateDiff(Max(Date of Sale),Current Date)?

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • angela2018
    angela2018

    So that is basically giving me the same results as a different beast mode that I have in this set (which tells me how many days it has been since the sale). But the issue is that each transaction currently has its own line, which means sales reps have like 12 lines each. I only want one line per rep stating the # of days since their most recent sale rather than how many days it has been since each of their sales.  

  • Chips
    Chips Domo Employee

    So the Max is aggregating at the lowest common denominator shown which in your case is Order Number AND Rep. 

     

    If you remove Order Number from the table (and any other Order identification), it should work. 

     

    If you have a Table Card that is simply the following two columns, it should work.

     

    Column 1 Rep Name

    Column 2 DateDiff(Max(Date of Sale),Current Date)

     

     

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • angela2018
    angela2018

    That gave me the sum of all days between today and A, B, C, D, E transactions (for example, someone who transacted as recently as last week came out to 246 days due to it aggregating all of his transactions). 

  • ST_-Superman-_
    ST_-Superman-_ Coach

    I've run into a similar issue when dealing with max and min aggregates with date fields.  Try changing the order a bit:

     

    Column 1 Rep Name

    Column 2 MIN(DateDiff(`Date of Sale`,Current Date)

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Categories