Hello-
I am new to posting in the forums, but have used the forum search often. I have found a question that seems to be stumping everyone. I am using Tableau desktop pro 2018.3.5.
I have a large and complex report that brings in several different data sources. I cannot share it because it is sensitive data and I have no idea how to recreate it, hopefully my question will be enough.
On one worksheet, I have 2019 and 2020 data with 4 category levels. The counts are by Month/Year. The dataset has the values down to the day. I need to create a standard deviation for all the values, across the view, using just the 2019 monthly data. There are null values for some months.
Once I can get a total for 2019, then I can use STDEVP. Then from there I will add color when the monthly amount is above that number. We are using 2 times the standard deviation.
This is what I currently have and it won't calculate the year correctly it only shows 1 for the upper standard deviation for all the values in 2019 and nothing for 2020, which makes sense, but I need to correct it.
IF MAX(YEAR([Date Completed])) = 2019
then (AVG([Count])+ STDEVP([Count])*2) END
Any guidance would be helpful. I have searched the forums and the lessons and tried many of the solutions but none exactly apply or work as expected.
I am only showing April 2019-April 2020 data but the rows are just hidden not filtered.
Thanks
Edithanne,
I took a stab at it, maybe this can give ideas.
I took the route of nested Level of Detail calculations.
I first used a conditional to only return values if they come from 2019.
Then I summed that count for each month-year.
Then for each Category I took the STDEVP.
{ FIXED [Category]:STDEVP(
{ FIXED [Category],[DateField (Month / Year)] : SUM(
IF YEAR([DateField])=2019 THEN [Quantity] END)}
)}
Please see workbook v2018.3 attached in the Forum Thread:
