hi Miguel,

So what (I think) we need to do here is average the stock of each company, over whatever time period and then add them together...and one way is to use an LoD calculation (LoDs allow us to tell Tableau the level at which to aggregate).

So in your example here...

{FIXED [Tikcer], [Sector]: AVG([MktVal])}

So this creates (for each Ticker/Sector) the Average [MktVal] (so at Day level it's the same as using SUM, as the AVG of 1 value is the same as the SUM of that Value...or MIN/MAX/MEDIAN for that matter!), but at higher levels (say Month) it takes the Average for each company over the month and then SUMs them up.

You can then bring this field in a SUM (as LoDs are returned at Row Level we are then able to re-aggregate), and that should work at any level.

Hope this does the trick, and makes sense, but let me know if not (and if it doesn't do as you want, I may need a sample workbook so I can take a look at the "Grain" of the data, as we may need to add some other dimension to our LoD, or we might need to weight the prices on Volume, for example)

hi Luis Miguel,

Sorry my bad...I forgot we also need to add the date part to the LoD!...else each company is just taking it's average over all time and adding them.

So I've mocked up (well downloaded) the Stock Price for 3 companies (so a mini version of yours). I'm not sure what control you want over the time period. I've chosen a parameter.

I've first set up a parameter

and created the following calculated field to return the requested time period

[Selected Period]

DATETRUNC([Select Period],[Date])

where [Select Period] is the parameter.

I then add this to the LoD...

[Close - LoD]

{FIXED [Company],[Selected Period]: AVG([Close])}

and now (I think!) it does what we want. If it's still not, can you just export the data for 2 or 3 of companies, so I can see the "grain" of the data.

I've attached a 9.3 version of the workbook here...

so the average will just take the average, over that of time (btw NULLS don't get counted in Average...so 01/01/2016 = $200, 02/01/2016 = $400, 03/01/2016 = NULL would give an average of $300 not $200). So the question is how you want to take the Market Value when you change from Day? Is it the Average of each company over the Month (added together)?, or the figure on the last day of every month...etc.?

I've downloaded the beast!...and adding the date period in does do what I'd expect (but what you want may be a bit different!!). If you can let me know how you'd expect these figures to be calculated at a Week/Month...etc level there's probably a formula (or formulas) we can use. One other thing was I didn't realise quite how big the data was! Fixed LoDs (although amazingly useful) aren't that efficient, so we may have to go with an INCLUDE/EXCLUDE or Table Calc to get it working as fast as possible.

Edit SR:- One other thing that occurred is to always keep day level, and overlay a moving average (in the below the blue line is a rolling average over the previous 30 days)...just a thought!!

Wow so this is only the sample!! (mind you I has 1.2 Billion rows the other day, although had to move out of TDEs and use EXASol Live!!)...

So yes it's often easier to isolate a small amount of the data (be that a few companies, or a single month) and get that right...then hopefully, maths being what maths is, should work for all the data!

So I have isolated July 2016, and below shows this by day, and then the values we get back with a couple of calculations for the month level.

I've also removed Sector from the LoDs (as from a quick look at the data, having it in doesn't seem to increase the grain).

Date1 - Month is simply DATETRUNC('month', [Date1])

Then

[Month LoD MktVal v1]

{FIXED [TICKER (all stocks since 1962.csv)], [Date1 - Month]: AVG([Mktval])}

So this one is the SUM of the Average for Each TICKER by Month. notice that the value changes on the 20th. This is (I'm guessing) that we have a new TICKER and as the AVG is worked out at this level there is one (or more) extra companies adding to the sum

[Month LoD MktVal v2]

{FIXED [Date1 - Month]: SUM({FIXED [TICKER (all stocks since 1962.csv)], [Date1 - Month]: AVG([Mktval])})}

and this one is a slight variation, taking the Average for each TICKER over the month, then SUMing those values up at the Month Level. As we force this one back to be returned at Month level, this one doesn't change.

Below I've attached an export of that table (so you can copy and paste into Excel). If you can look at the day level MktVal and let me know what value you'd expect to see for July, 2016...it gives us something to aim for (and we can de-bug if it's not quite doing what it should!...and hopefully work out why)

Hi guys,

Huge dataset, great to explore, thank you!

Pardon for a dumb question, but may be this could help?

SUM([Mktval]) / COUNTD([Date1])

Yours,

Yuri

Pardon for a dumb question, but may be this could help?

...As always Yuri, such clear thinking!!...I think the phase (on my attempts)..."can't see the wood for the trees" (if you know the expression) springs to mind!! - sometimes you can get so caught up with the data/calculation grain and getting the LoD levels...that the obvious just sails on past!!

And this, of course, will be so much faster.

Yes, it is a cool data set...

Thanks again for the intervention!!

A grain of salt here:

The data is sparse.

There are days (plenty of) for which

some stocks have no trades, hence no data.

This is no problem for a rough estimation

of the whole Market Cap at 2016,

but could be an issue when segmenting.

Cheers,

Yuri