9 Replies Latest reply on Sep 25, 2016 2:55 AM by Yuriy Fal

    Average Volumes - Daily, Monthly, Weekly & Yearly

    Sagar Agarwal
    Sagar Agarwal Sep 2, 2016 3:44 AM

      Hi All,

       

      I sense that this is easy but not been able to get through - I need to calculate Average Volumes for all date dimensions - daily, weekly, monthly, yearly.

      I checked some forum posts to calculate the number of business days which I did, but i am unsure how do i use to calculate the average volumes.

       

      Below is how my data looks:

      Yellow highlighted means that i have 2 entries for the same date - so in that case the avg daily volumes will change.

      Green highlighted means that I also have some business days missing from my data- so in that case, I have to use the actual number of days that is present in data (Its fine if we ignore this detail, but if it can be done, then great)

       

      I have to show this avg volumes column as raw data in my viz, so its fine if it repeats for the concerned rows. Screenshots below:

       

       

       

       

        • 1. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
          Yuriy Fal
          Yuriy Fal Ambassador Sep 6, 2016 8:39 AM (in response to Sagar Agarwal)

          Hi Sagar,

           

          If I understand your question correctly,

          you would like to see "Average of Averages",

          i.e. making an Aggregate of another Aggregate.

           

          For a problem like this a common approach is

          using Level-Of-Detail (LOD) expressions.

           

          Please find the attached as an example (using Superstore).

          An in-a-Day average Order Sales is used as a "base" aggregation

          (to mimic more than one row for a particular day in your datasource).

           

          Those "base" (Daily) aggregate calculation is further aggregated

          (averaged) per Week, Month and Year, taking into account

          your notion of 'working days', where no rows for a particular day

          means no sales (not zero ones), so that days shouldn't be counted.

           

          Yours,

          Yuri

          2 of 2 people found this helpful
          • 2. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
            Sagar Agarwal
            Sagar Agarwal Sep 8, 2016 6:21 AM (in response to Yuriy Fal)

            Hi Yuriy,

             

            Thank you for your response. Just a heads up - I will be checking this today and I will share the results

            • 3. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
              Sagar Agarwal
              Sagar Agarwal Sep 14, 2016 2:17 AM (in response to Yuriy Fal)

              Hi Yuriy - apologies for the delay. When I got back to implement the solution, I realised that my need was a bit different. But your LOD calculation showed me the way to do it. Below is what I did:

               

              I wanted to calculate the Avg Volumes - Weekly, Monthly, Yearly, etc. based on how I have set the Date Pill & I wanted to calculate these values based on a dimension (which will be user selected). This is how I setup:

               

              The "Dimension Level 1" takes input from a Parameter, and hence calculates the Avg Volumes for that dimension based on Trade Date field. I am changing the Date Level dynamically using the "Aggregate By" parameter (in the 2nd screenshot). This is working perfectly fine for me, but I have another challenge - I want to rebuild this sheet in v8.2 (want to publish to Tableau Server v8.2) and as far as I remember, LOD calculations are not present in v8.2. So how can I implement this without LOD calculations?

               

               

              • 4. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
                Yuriy Fal
                Yuriy Fal Ambassador Sep 14, 2016 2:33 AM (in response to Sagar Agarwal)

                Hi Sagar,

                 

                Common approaches of the "pre-9 era"

                were either Table Calculations or Data Blending.

                Which one to choose is a matter of taste

                (actually, the shape of the data dictates which).

                 

                If you wouldn't hesitate to share your workbook here,

                I would be eager to help. Though I have no version 8.2

                installed on my Mac right now, having the data from you

                I would give it a try.

                 

                Yours,

                Yuri

                • 5. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
                  Sagar Agarwal
                  Sagar Agarwal Sep 14, 2016 2:54 AM (in response to Yuriy Fal)

                  Hi Yuriy,

                   

                  I cannot share the workbook / original data but I can certainly create a sample data file (and the workbook) and share it with you later today. Thank you

                  • 6. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
                    Sagar Agarwal
                    Sagar Agarwal Sep 14, 2016 4:21 AM (in response to Yuriy Fal)

                    HI Yuriy,

                     

                    Please find attached a workbook I made using sample data. In the original data, I have approx 25 columns with figures, but for the sake of simplicity, I have added only 2. The data is more or less similar.

                    I need to calculate the average daily volumes - weekly, monthly, quarterly, yearly - based on what I select in the "Aggregation level" dropdown, without using LOD technique. I would really prefer Table calcs instead of blending because I have many other filtering actions to build on this view then, so prefer to keep only one data source.

                    Let me know if you need any more details. Again, thank you very much for your time and help

                    • 7. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
                      Yuriy Fal
                      Yuriy Fal Ambassador Sep 18, 2016 5:25 AM (in response to Sagar Agarwal)

                      Hi Sagar,

                       

                      I am a bit unsure about the "Average Daily Volumes",

                      since your data has only one row for each Instrument.

                      So I would interpret the above as something like

                      "Average Volumes among Instruments trading in the same Period".

                       

                      Please find the attached.

                       

                      Yours,

                      Yuri

                      • 8. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
                        Sagar Agarwal
                        Sagar Agarwal Sep 20, 2016 1:36 AM (in response to Yuriy Fal)

                        Hi Yuriy,

                         

                        I am sorry for I didn't know that my question was ambiguous - what I really wanted was Avg Volumes per Instrument for that week / month. I have aggregated the data using Alteryx and it essentially has only one row per instrument per date.

                        So if an instrument A traded 3 times in a week & 15 times in a month, the Average Volumes for that week will be SUM(Volumes)/5 and Average Volumes for the month will be SUM(Volumes)/22 (or 20) - depending on number of business days.

                         

                        I hope I am clear now I will be working with your workbook and will let you know if i was able to derive it from your solution.

                         

                        Thanks.

                        • 9. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
                          Yuriy Fal
                          Yuriy Fal Ambassador Sep 25, 2016 2:55 AM (in response to Sagar Agarwal)

                          Hi Sagar,

                           

                          Does it mean that your [Sample Transaction Data] datasource

                          (which contains only one row per Instrument for a whole dataset)

                          is not representative enough for your case?

                           

                          Could you please get it to include the full set of rows for a given Instrument?

                           

                          Does your working dataset include rows for every Instrument on every workday --

                          with Zero volumes maybe when the Instrument has no trades on a particular day?

                          Such kind of dataset is called a periodic snapshot. It is best suited for your task.

                           

                          Or may be you get only whose days when your Instrument is actually traded?

                          This is called a transactional aggregate.

                          For the latter one a table of workdays is needed to join with.

                           

                          The former or the latter, the calculation could be the same, like this:

                           

                          SUM([Volume]) / COUNTD([Workdays])

                           

                          Yours,

                          Yuri