10 Replies Latest reply on Jul 12, 2018 11:37 PM by Levan Uridia

    Weekly Stdev with Daily Data

    Cedric Crucke

      Hello everyone,

       

      New to Tableau but picking it up rather quickly and loving it. However, now that I am trying to do more complex calculations, I'm struggling....even with the help of the almighty Google.

       

      Situation:  I have DAILY | CUSTOMER | PRODUCT | DC data in my data set. I would like to do a standard deviation of WEEKLY data. When I do a typical STDEV formula, it goes to the low level and calculates it - totally understand why. I have created a formula that aggregates up to weekly values { INCLUDE [Fiscal year]: AVG({ INCLUDE [Fiscal_week_year_nr]: SUM([Units])})} however, when I attempt a STDEV on that data set, it yields NULL.

       

      I have also tried numerous other methods using INCLUDE and EXCLUDE and my results are either 0 or NULL.

       

      What I would like is to have the STDEV of the AGGREGATED WEEKLY UNITS. For example, two weeks of data, Week 1 = 10,000 Week 2 = 11,000 Week 3 = 7,000, calculate the STDEV off those values and not the underlying 1's and 3's and 4's being sold to the low level.

       

      As mentioned earlier, I have spent hours googling and then realized a lot of the links were coming to this community board (which I did not know existed as I am brand new) and hoping for some expertise from all of you.

       

      Many thanks in advance!

       

       

        • 1. Re: Weekly Stdev with Daily Data
          Jim Dehner

          Good morning Cedric

          It would help to see your twbx workbook with the data included

           

          I am a little confused about the data elements that make up the set from which you want to determine the standard dev - are you saying that you want the standard deviation across all the orders that were in week 1 and then all the orders that were in the combined 2 week period week 1 and 2 etc? 

           

          I can't see in you formula that you are getting an array of values - just a single number for the week

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Weekly Stdev with Daily Data
            Cedric Crucke

            Jim,

             

            Thanks for your reply. I'll attach a sample Excel file with an example as the data I have in the workbook is sensitive.

            • 3. Re: Weekly Stdev with Daily Data
              Jim Dehner

              Thanks -

              please see the video at the link - on how to anonymize your data

               

              Anonymize your Tableau Package Data for Sharing

               

              sharing an excel file wont demonstrate the problem you are having

               

              Jim

              • 4. Re: Weekly Stdev with Daily Data
                Cedric Crucke

                Jim,


                Thanks for that. Saved some subset of the data and created a new workbook. There are three columns:  Week | Stdev of Units | Units

                 

                 

                As you can see, the Stdev is working at a customer | product | week level. My desired output would be a Stdev across all the weeks at the aggregate level (the value would be the same across all of the weeks). For example, I did this in Excel and I get a value of 39.7 (which feels right when you look at the aggregated weekly values.

                 

                Hopefully this helps bring some clarity!!


                Thanks!

                • 5. Re: Weekly Stdev with Daily Data
                  Levan Uridia

                  Hi Cedric

                   

                   

                  Here is the calculation you need

                   

                  { FIXED YEAR([F1]): STDEVP([Weekly Sales])}

                   

                  the workbook is also attached.

                   

                   

                  If it solves your question please mark it as a correct answer.

                  • 6. Re: Weekly Stdev with Daily Data
                    Cedric Crucke

                    Thanks - getting close!

                     

                    I see how this is working, however, i should have noted that I only have the daily sales off which to work. I don't have the Weekly Sales column in my other raw data, I was calculating that to show what I meant...

                     

                    That said, in Tableau I do have a calculated Weekly Sales formula:  { INCLUDE [Fiscal year]: AVG({ INCLUDE [Fiscal_week_year_nr]: SUM([Units])})}  I thought this would allow me to do what you have done, but without that column in the raw data. However, when I do your formula using this calculated measure, my StdDev shows up as 0.

                    • 7. Re: Weekly Stdev with Daily Data
                      Cedric Crucke

                      I see now that your workbook was different than what I had originally sent. Could you take a look at that one and then let me know how you would go about doing this, please?


                      Thank you!

                      • 8. Re: Weekly Stdev with Daily Data
                        Levan Uridia

                        you can calculate weekly sales by creating calculated field

                         

                        {Fixed [F2]: Sum([Daily Sales])}

                         

                        I don't have your version of tableau so I can not open your workbook

                        • 9. Re: Weekly Stdev with Daily Data
                          Cedric Crucke

                          Yeah i have the weekly sales calculation done...using that formula above. I'm staying away from Fixed and using Include instead as I will like to filter it. But when I use the calculated Weekly Sales to do STD Dev, it doesn't work - it gives me "0"

                          • 10. Re: Weekly Stdev with Daily Data
                            Levan Uridia

                            Hi Cedric

                             

                            I am not sure how your weekly sales work. INCLUDE function usually is used to specify which fields to be used in a calculation.

                             

                            In your example: { INCLUDE [Fiscal year]: AVG({ INCLUDE [Fiscal_week_year_nr]: SUM([Units])})} I do not know what are [Fiscal year], [Fiscal_week_year_nr] and [Units]

                            and unfortunately I can not open your workbook. Most probably your level of detail is either to single entry (daily) or to the whole week where all entries are the same in both cases Mean equals each entry and STD = 0. If you could write same include based on your example from excel i could try to help.

                             

                            Otherwise if you use {Fixed [F2]: Sum([Daily Sales])} for weekly sales it works fine.

                            I do not understand why are you avoiding FIXED what kind of filtration is prevented by FIXED?