1 Reply Latest reply on Nov 30, 2016 1:35 PM by Yuriy Fal

    Totals between two dynamic dates?

    Aaron Steppe

      I am working with mental health data. Every individual receives a treatment plan every few weeks, wherein his or her plan of service changes (or doesn't! I don't have access to that data). Regardless, there are various procedures they are expected to receive a certain number of per week. What I want to do is find:


      The total number of units received between treatment plans

      The total amount of units received in each whole, completed week between treatment plans

      The highest weekly amount of units received between treatment plans

      The lowest weekly amount of units received between treatment plans


      Moreover, I want to calculate the same values when the individual has not had a treatment plan since the most recent (I think using the endpost Today() should work). I can go back to SQL and do some custom SQL there as this is a bit of a complicated gaps and islands problem, but I'm pretty sure Table Calcs should be able to accomplish what I want.


      My thought process:


      1.) Find whole weeks between treatment plans can use the dateadd function: add one week to Treatment Plan Date, calculate all Units between the two dates using previous logic. Create a measure which is weeks between the two dates elapsing from the Treatment plan and going forward to today/the next treatment plan. Then find the total number of units in each week.


      2.) The other aggregates (average, min, max, sum) can be calculated from the weekly amounts. Again, Treatment Planning doesn't happen on any consistent day, so this would have to be recalculated each time a plan happens for each individual, and there are thousands.


      Can I easily use Tableau to calculate the amounts between dates? Or should I use SQL to set up the bounds and do the weekly aggregation, as the request shouldn't get more granular than week. I'll try to come up with some test data