2 Replies Latest reply on Mar 28, 2012 12:47 PM by ram.brahmachari

# Calculated field : Mixing aggregate and non aggregate arguments

Hi,

Please see the attached excel spreadsheet. I have a table of stock portfolios, where the key is Date + Portfolio code + StockID. Thus for each date, I would have several portfolios, each of which would have many stocks. Of course, the stocks would repeat between portfolios, as the portfolios would repeat between dates.

Let's  say I have two measures - Stock Risk and Beta.

Question 1

I want to create a calculated field that divides the stock risk for a given stock within a given portfolio for a specific date (effectively the stock risk measure from a single record in the stock table) by the sum of all the stock risks for that portfolio for that date.

Thus I want to calculate a percent of risk for each stock (the 30% number in the attached sheet). How could I do this? Tableau says can't mix aggregate and non aggregate items !

Question 2

Similarly I have a Factor table as shown in the attached excel sheet - date, portfolio code and Factorname together being the key. How could I take one record from the factor table (i.e. factor risk for a single factor from a given portfolio for a particular date) and divide it by the SUM of all the stock risks for that portfolio and that date - (the same denominator as in Ques 1) - the 15.6% in the attached sheet.

For the denominator, Tableau instead uses the sum of all the stock risks from the stock table - it doesn't filter by the date and the portfolio code. I have these 2 filters in my worksheet. They seem to only effect the numerator - the industry risk. I get the industry risk for only one industry (top risk industry) for the chosen portfolio and the chosen date.  The filter however doesn't effect the measures used from my secondary table (stock table) !

Question 3

I would like to have a scatterplot of the sum of all stock risks (X axis) against the average of the Betas (y axis). Once again this is for all stocks within a given portfolio for a certain date. I have 2 filters on my workspace - date and portfolio.

When I choose a certain date and a certain portfolio, everything works just fine - I see a single point on the chart.

I was hoping that when I choose ALL portfolios, for a certain date, I will see a nice scatter plot for all of my portfolios, each point representing the intersection between that portfolios Beta and Risk! Instead, I see only one single point, which is the sum of all risks, and average of all betas across ALL portfolios for that date.

I have very recently started using Tableau. Hopefully you guys can provide me the right directions.

Regards,

Ram.

Q

 Date Portfolio Code SecurityID Stock Risk Beta 3/26/2012 A AAPL 0.78 1.3 3/26/2012 A GOOG 0.52 1.35 3/26/2012 B AAPL 0.52 1.3 3/26/2012 B GOOG 0.39 1.35 30.0% 3/26/2012 B IBM 0.13 1.1 3/26/2012 B MSFT 0.26 1.15 3/26/2012 C IBM 1.3 1.3 3/27/2012 A AAPL 0.65 1.3 3/27/2012 A GOOG 0.65 1.35 3/27/2012 B AAPL 0.39 1.3 3/27/2012 B GOOG 0.52 1.35 3/27/2012 B IBM 0.39 1.1 3/27/2012 C IBM 1.3 1.3

Date
Portfolio Code
SecurityID
Stock Risk

 Date Portfolio Code SecurityID Stock Risk Beta 3/26/2012 A AAPL 0.78 1.3 3/26/2012 A GOOG 0.52 1.35 3/26/2012 B AAPL 0.52 1.3 3/26/2012 B GOOG 0.39 1.35 30.0% 3/26/2012 B IBM 0.13 1.1 3/26/2012 B MSFT 0.26 1.15 3/26/2012 C IBM 1.3 1.3 3/27/2012 A AAPL 0.65 1.3 3/27/2012 A GOOG 0.65 1.35 3/27/2012 B AAPL 0.39 1.3 3/27/2012 B GOOG 0.52 1.35 3/27/2012 B IBM 0.39 1.1 3/27/2012 C IBM 1.3 1.3

Beta

3/26/2012
A
AAPL
0.78
1.3

3/26/2012
A
GOOG
0.52
1.35

3/26/2012
B
AAPL
0.52
1.3

3/26/2012
B
GOOG
0.39
1.35
30.0%

3/26/2012
B
IBM
0.13
1.1

3/26/2012
B
MSFT
0.26
1.15

3/26/2012
C
IBM
1.3
1.3

3/27/2012
A
AAPL
0.65
1.3

3/27/2012
A
GOOG
0.65
1.35

3/27/2012
B
AAPL
0.39
1.3

3/27/2012
B
GOOG
0.52
1.35

3/27/2012
B
IBM
0.39
1.1

3/27/2012
C
IBM
1.3
1.3

Hi,

• ###### 1. Re: Calculated field : Mixing aggregate and non aggregate arguments

Ram, try the following attachment. Don't know if question 3 was what you were looking for. Please check all of the values.

1 of 1 people found this helpful
• ###### 2. Re: Calculated field : Mixing aggregate and non aggregate arguments

The following formula works well when my workspace shows all the stocks

attr([Stock Risk])/total(sum([Stock Risk]))

But,  I have a top filter that shows only n (user chosen parameter) risky names from the portfolio.

The % stock risk should be a fixed number for each stock in a portfolio for a given date. It's the stock risk divided by the sum of risks for all the stocks. Sometimes I want to show only the riskiest stock, sometimes the 5 riskiest ones. The % of stock risk for each stock, should not vary becuase I am choosing a different value of n.

I have 2 other filters, date and portfolio. I have done an "Add to Context" on these. Thus they are applied first. This has solved a big part of the problem. Now the graph shows the n riskiest stock for the portfolio for the given date. Without these 2 filters pinned, I was getting the 5 largest across all porfolios and all dates.

However, I am still having trouble calculating the % of stock risk.

Thanks.