2 Replies Latest reply on Mar 1, 2017 12:07 PM by TJ Kellogg

    Sales Commission - How do I create a date filter for a date range based on a minimum date?

    TJ Kellogg

      Essentially what I need to do is identify invoices that were created within 31 days of each client's first invoice. It seems like a simple task, but I am a fairly new Tableau user and each calculated field I've tried to create gives me an aggregate/non-aggregate mix error (I've taken many approaches but keep ending up with some variation of: "IF invoice date - min(invoice date)<32 THEN......").


      It would also be helpful to then be able to identify the max invoice date from invoices fitting the above parameters.


      Goal of these functions is to map a sales commission structure - commissions are based off of the margins accumulated on invoices from a client's first 31 days on board, and are paid at the end of the month during which the client's 31st day of tenure occurs. For example - if a Client's first invoice was funded on January 30th, the commission would be based on margins accumulated from January 30th through March 2nd, and the commission from that Client would be paid out at the end of March.


      My hope is that I can create a report that can be run at the end of each month to automatically calculate the total margins for each of our sales representatives. So if I ran the report described in the example above at the end of March, it would identify all 31 days worth of invoices back form the ending date on March 2nd to the initial date of January 30th.


      I've attached a small sample workbook with sample data for a sample representative.... any assistance and/or suggestions on how to bring this project to life are greatly appreciated!