-
1. Re: Aggregation by dates
Alex Kerin Jan 24, 2012 3:10 PM (in response to Luis Amadeo)Mock up some data and the expected result from that data - I'm sure we can help
-
2. Re: Aggregation by dates
Luis Amadeo Jan 24, 2012 7:50 PM (in response to Luis Amadeo)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,
-
mockdata2.xls 30.5 KB
-
-
3. Re: Aggregation by dates
Alex Kerin Jan 25, 2012 6:54 AM (in response to Luis Amadeo)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
-
timebetween.twbx 51.7 KB
-
-
4. Re: Aggregation by dates
Jonathan DrummeyJan 25, 2012 8:43 AM (in response to Luis Amadeo)
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
guest contributor Jan 25, 2012 12:47 PM (in response to Luis Amadeo)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
Alex Kerin Jan 25, 2012 3:13 PM (in response to Luis Amadeo)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.