1 2 Previous Next 16 Replies Latest reply on Feb 22, 2016 5:25 AM by Jonathan Drummey

# How to perform SUM on an aggregated function? Any alternative?

I have created an aggregated function using calculated fields, and now when I try to perform a sum operation it doesn't allows me to do so.

Are there any alternatives or solutions to this?

Thanks,

Dipesh

• ###### 1. Re: How to perform SUM on an aggregated function? Any alternative?

Make your first aggregation an LOD expression, then you can aggregate an aggregate. Something like this:

SUM( {FIXED : SUM([Sales]) }

You'll need to experiment with different aggregations to get the value you're looking for.

--Shawn

• ###### 2. Re: How to perform SUM on an aggregated function? Any alternative?

table calculations offer aggregates of aggregates - window_sum()

More help for learning Table Calcs here:

http://www.tableau.com/learn/tutorials/on-demand/table-calculations-0

Advanced Table Calculations & Formulas Video | Tableau Software

Tableau Resources | Health Intelligence

Or search the forums for more:

I hope this helps

Patrick

• ###### 3. Re: How to perform SUM on an aggregated function? Any alternative?

Thanks Shawn, I will try it that.

• ###### 4. Re: How to perform SUM on an aggregated function? Any alternative?

Thanks Patrick, I will try the window_sum() function.

• ###### 5. Re: How to perform SUM on an aggregated function? Any alternative?

I tried using both the LOD expression and window_sum() but it didn't help.

My calculations are a bit complex.

So, I have already created 2 aggregated function say A & B and then I create another aggregate function say C which has an IF THEN condition containing A > 0 & B > 0 giving results as 1 or else 0.

Now I need to sum all the 1s and 0s of the function C. Basically SUM(C)

In window_sum(), I need to use a table calculation which is C containing two other aggregate calculation A&B which is not working.

Same is the case with {Fixed: Sum(C)}, cannot use a table calculation.

Is there any way out to this?

• ###### 6. Re: How to perform SUM on an aggregated function? Any alternative?

Got a packaged sample workbook? What version are you using?

--Shawn

• ###### 7. Re: How to perform SUM on an aggregated function? Any alternative?

Hey Dipesh, you've accidentally posted a data extract (.tde) as a zip file:

Instead, open up the workbook (make sure you've extracted all the data connections) and then go to File\Export Packaged Workbook... That's the workbook we'll need to figure out what you're trying to do.

Cheers,

--Shawn

• ###### 8. Re: How to perform SUM on an aggregated function? Any alternative?

Thanks for the packaged workbook. At first glance, one of your problems is that you are using more than 6 Discrete pills on the Row shelf, so two of your fields are being Grouped:

Anytime you see a comma like that, it means T is grouping them. Easy fix change the Row levels to 16 -- the max T allows (sort of):

It's going to take a bit more time to study the Table Calc issue because it's not just

... that you are asking for. You actually want to Sum a Table Calc. And not just that, you want to

# Sum a Nested Table Calc!

I don't really have the energy (or inclination) to wrestle with this today, especially since I know Jonathan Drummey can do it in a fraction of the time I would spend on it. If he (or someone else) doesn't get to it, I'll take a whack at it this weekend.

Cheers,

--Shawn

• ###### 9. Re: How to perform SUM on an aggregated function? Any alternative?

Oh its more complex than I thought at the beginning.

Thanks a lot for the explanation. That will really help a lot.

It would be great if you can do it anytime during the weekend. Jonathan Drummey your help is appreciated.

I have been stuck on this for a while now.

Thanks,

Dipesh

• ###### 10. Re: How to perform SUM on an aggregated function? Any alternative?

@Shawn, you're right, this is complicated.

@Dipesh: I've got a few questions because my first inclination would be set up the data source such that the prior day's close was available in each record so that way you wouldn't need table calculations. So...

1) What is the raw data source? Excel or something else?

2) I can see in the workbook you are using a Tableau data extract, are you planning to use that in production?

3) Do you have the ability to add tables to the raw data source, in particular a table identifying trading days?

4) Do you have the ability to write SQL (or have someone working with you who can?)

5) How many socks & trading days are you going to have in your data set?

Jonathan

• ###### 11. Re: How to perform SUM on an aggregated function? Any alternative?

Hi Jonathan,

1. Excel

2. Yes

3. No changes can be made to the raw data, as it is directly exported as an Excel file from the DB

4. No SQL. Have to work with the given data without additions in the raw data, but yes I can make changes in tableau like add new column etc.

5. Currently this is a sample assignment I am doing.The size would be the same.

Thanks,

Dipesh

• ###### 12. Re: How to perform SUM on an aggregated function? Any alternative?

I added two calcs: WINDOW_SUM([up day Bot 1/0]) and WINDOW_SUM([down day Bot 1/0]), with the default compute using of Table (Down) in this view they both work:

Note that instead of a bunch of discrete pills I'm using Measure Names/Values to lay out the table.

Jonathan

• ###### 13. Re: How to perform SUM on an aggregated function? Any alternative?

Thanks a lot Jonathan. This did simplify the dashboard a lot but the outcome I was looking for is a bit different. I wanted to sum the up day1/0 and down day 1/0 for each utd stock for the entire date period. when I select all of them it gives me a common sum for every value.

The range should be between 0-7. Is this possible?

The Window_sum method would definitely help here, but I just can't reach that point.

Thanks,

Dipesh

• ###### 14. Re: How to perform SUM on an aggregated function? Any alternative?

You need to change the compute using to the date dimension so it partitions on each stock.

Jonathan

1 2 Previous Next