2 Replies Latest reply on Aug 1, 2017 1:24 PM by Patrick Van Der Hyde

    Calculation based on *all* dates in dataset?

    Sebastian Chavez

      I can't share my workbook because of confidentiality protocols, but I'm on Tableau Desktop 10.2.


      I have a dataset where each row corresponds to a single record, and each record contains a date and the name of a laboratory. I have a chart which attempts to show the Count, Window Average, and Z-score for each facility, per week.


      When the chart is showing everything in its default view, all the calculations work fine. But when I filter to only look at a particular laboratory, the calculations get thrown off if there is no data for that laboratory on either the first or last week in the dataset. For example, if I have a laboratory with weekly counts that look like this:


      Wk1:     1 rec

      Wk2:     0

      Wk3:     0

      Wk4:     0

      Wk5:     1


      It will always calculate my Window Average and Z-score based on 5 weeks (in this case 2/5, or 0.4/week). But if I have a submitting facility with weekly counts that look like this:


      Wk1:     0 rec

      Wk2:     1

      Wk3:     1

      Wk4:     1

      Wk5:     0


      It will calculate my Window Average and Z-score based on 3 weeks (3/3, or 1.0/week) unless other labs with data on Week 1 & 5 are also in the view, in which case it will calculate based on 5.


      Is there a way to force my calculations to always reference the total number of weeks in the dataset, even if that particular lab doesn't have data on the first and last dates?


      In the first screenshot, the long pink bar extending left corresponds to (let's say) Example Lab for the most recent week, Week 29, and has a zero records that week, with an average of 2.5 records/week (30r/12w). But if I filter by Example Lab (second screenshot), it shows me an average of 2.7, because it isn't counting Week 29 since it had no values and was at the end of the date range. I would like the second screenshot to also show a 2.5 average, and to also have a row for Week 29 matching the one in our first screenshot. It seems like I'd need some kind of LOD calculation but I can't figure out how. (Note: the first screenshot is from the workbook, the second is from the dashboard)


      Thank you!!!