-
1. Re: Aggregation by dates
Mock up some data and the expected result from that data - I'm sure we can help
-
2. Re: Aggregation by dates
Thanks for your quick response. I am attaching so mock data. It's not all but just the necessary for the calculation. Substituted the name for an ID, so each Client ID is one individual. There is an episode start time and episode end time. So I want to calculate the number of days between each episode. I also would want to know the average # of days between episodes for each client. Hope this helps. Thanks,
-
3. Re: Aggregation by dates
Okay, attached, with caveats - I am not great at window calculations, so there could easily be situations where my solution breaks (but not obviously, so check numbers). Richard L, Joe M, and others are much better.
Look carefully at how the window calcs are set up, especially how you order the data to do the lookup. The complex "if then" of the average calculation is simply to get tableau to not overwrite the same text and minimize the number of calcs it does - window_avg([Time between] is the calculation
-
4. Re: Aggregation by dates
Jonathan Drummey
Jan 25, 2012 8:43 AM
Alex, thanks for the complex "if then" in the average calculation, I'd been wondering how to get Tableau to not overwrite text!
-
5. Re: Aggregation by dates
Alex, thanks a bunch for this. The numbers do check. With the larger set of data that I have it gets tricky but for my purposes this will work. I don't think I can add another dimission as it may not work, e.g., if I add the patient's unit and wanted the aggregation by unit. But this is fine, thanks
Luis A.
-
6. Re: Aggregation by dates
You can add another dimension, you just have to change the partitioning of the window calcs. If you edit the table calc (right click on the measure on the row pane) you'll just have to move the new dimension over with the rest.