1 of 1 people found this helpful
So you're looking for the percent of DTTM that have Max(Seconds)>=900? I.E. 3 out of 5?
First, create a calculated field similar to the following:
if max(Seconds)>=900 then (countd([DTTM])) end
Then, create a second calculation:
Right click on Calculation2 and select Default Properties>Number Format...>Percentage.
Hope this helps!
Thanks for your response. It is a step in the right direction, but I neglected to mention that I need the calculation to vary depending on the chosen timeframe for the visualization. That is, the user should be able to slice the data by hour, day, week, month, year, etc., and the percentage(s) should update accordingly.
Thus, if they are looking at the above data for the entire day of 7/13/13, they should see a single value: 60%.
However, if they choose instead to look at the data by hour, they should see a different value for each hour (in the example: 100%, 100%, 100%, 0%, 0%).
In reality, there are thousands of data points spanning several years. A common use case will be for the user to start with a single value for 2013, then drill down to one value per month, then week, then day, until they are finally looking at the individual rows in my original table. I would like for the user to be able to drill down by expanding (or contracting) the date field with Tableau's "+" button.
Is there a way to generalize your above solution to achieve this?
I've attached a packaged workbook. It has two data sources: Sheet1 and Sheet2. Sheet1 is a subset of the real data I need to use to create my visualization. Sheet2 is a manipulation of the Sheet1 data that I use to show what the visualization should look like. I need to be able to create the visualization directly from the Sheet1 data source (because it is infeasible to create an equivalent of Sheet2 for the real data set).
Any help will be greatly appreciated!
See the attached. I didn't try to completely follow your logic on what's a pass vs. fail, I set up the basics here. It uses a table calculation to aggregate over the MAX(Seconds) to calculate the % of DTTMs, I set the compute using to the DTTM but it could just as well be Table (Down) in this view. This will work as you click on the + sign to drill down the date hierarchy.
Thanks for the attempt to solve my problem. I took a look at it, but unfortunately, it still counts the MAX(Seconds) across all data points and uses that as the numerator in calculating the % of DTTMs. What I need is to compare the MAX(Seconds) across each distinct DTTM, then sum the number of those that are below (or above) 900 and use THAT as the numerator (the denominator being the number of distinct DTTMs rather than the number of rows).
So, in the example you sent, the value for "% of DTTMs" should be 60% rather than 100%.
I think I'm starting to understand this now. What defines a DTTM, exactly? Is it the time down to the hour, or something else?
Currently, a DTTM is the time down to the hour, but it is feasible that it would be down to the minute in the future.
(DTTM is my shorthand for "datetime," btw)
See the attached. What I did was duplicated the DTTM dimension so that there's a "DTTM for drill" that is used to drill up and down the hierarchy. The % of DTTM calc is modified to only return one value per partition, and still has a compute using on DTTM. That way it will partition on the DTTM for drill dimension. I also modified the DTTM Flag calc so it computes according to your original spec, and put a copy of the % of DTTM calc on the Filters Shelf, filtering for non-Null values. That way it won't end up with mark stacking.
Thank you so much - this is a very clever solution! I am integrating your solution into my worksheet and will report back to confirm that it worked or ask another question .
Thanks Jonathan! I was able to adapt your solution into what I needed.