-
1. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
Yuriy FalSep 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
-
AVG_of_AVG_LOD_YF.twbx 1.3 MB
-
-
2. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
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 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 FalSep 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 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 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
-
Avg Volumes - Sample.twbx 1.8 MB
-
-
7. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
Yuriy FalSep 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
-
Avg Volumes - Sample_YF.twbx 1.8 MB
-
-
8. Re: Average Volumes - Daily, Monthly, Weekly & Yearly
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 FalSep 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