15 Replies Latest reply on Jul 18, 2018 9:16 AM by lashon gaines

# How to aggregate an aggregation

Hello,

I am using Tableau 10.2 and I am having an issue aggregating a set of data because its already aggregated the totals seem to add instead of subtract.  I created these calculations and I need the variance between the two. I tried using subtract but it seemed to add anyway.

SUM({FIXED[Group Name],DATEPART('month', [Last Certified]) :COUNTD(IF Year([Last Certified])=2018

THEN [Plan #]END)})

SUM({FIXED[Group Name],DATEPART('month', [Scheduled Due Date]) :COUNTD(IF Year([Scheduled Due Date])=2018

THEN [Plan #]END)})

My Final Calc:

[month completed by grp]-[month scheduled by grp]  THIS GAVE ME THE TOTAL BUT IT SEEMED TO ADD IT INSTEAD OF SUBTRACT IT.

I am looking to subtract the top calculation from the bottom one to get the variance between the two. Can someone please assist?

• ###### 1. Re: How to aggretate an aggregation

Hi

without your twbx workbook with data attached this is working in an abstract -

so you will need to to adjust for syntact errors

first the LOD expression ist selv is not an aggregate (LOD expressions require you to include an aggregation clause but they are not aggregates in themselves

so the aggregation starts when you sum() the expression

what happened when you just tried to subtract expressions above?  If you are getting a Cannot aggregate an already aggregated expression you have 2 options - 1 is to create a nested LOD the combines the 2 above expressions - or you can use table calculations like window_sum

the choice is yours and will be driven by the other data in the viz, filters and you goals

sorry could be more specific with the twbx workbook

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: How to aggregate an aggregation

Here is my mock up workbook  please see the calculation included.

• ###### 3. Re: How to aggregate an aggregation

When I subtract the above it seems to be adding the totals

• ###### 4. Re: How to aggregate an aggregation

Hi and Thanks

the formula are working properly but you have a number of Nulls in the data set that are affecting the results  this is your data in the underlying table for the viz The question is why are there nulls and what do you want to do with them - include the values or exclude the values?

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 5. Re: How to aggregate an aggregation

I would like to exclude any nulls how do I adjust the calculation to exclude them?

• ###### 7. Re: How to aggregate an aggregation

I mean, I want to exclude the null last certified dates.

• ###### 8. Re: How to aggregate an aggregation

Please see the sheet titled "ent" those are the correct raw totals I just need the difference between them so I duplicated the individual sheets so that I won't affect the overall totals. The calculation that uses the Last Certified Date will include nulls that I need to exclude in order to get the correct variance of the raw totals.

Seems like this version makes simple subtraction a little harder than the newer versions but I can only use the company approved version. Basically I need my final calculation to be the total entries using the last certified date "minus" the total entries using the scheduled due date, without null values. I used ZN at first in the calculation and it inflated the numbers even more instead of subtracting them as well as window_sum. I'm a tad bit confused.

• ###### 9. Re: How to aggregate an aggregation

OK

you can just exclude then - then place the Add the filter to context also you had a second calculation 1 that calc is this formula which is summing the 2 values instead of taking the difference that is where the incorrect results come from - not the nulls Jim

• ###### 10. Re: How to aggregate an aggregation

Do I exclude them in the initial calculation or the final calculation. The initial calculation gives me the exact raw totals for some reason. I’m afraid if I change that my raw totals will change and I will have to start all over.

Regards,

LaShon A. Gaines

Operational Metrics Analyst

Information Security Risk Assessment and Consulting (ISRAC – COE)

Enterprise Information Security (EIS)

Office: 980.701.5630  |  Email: lashon.gaines@WellsFargo.com<mailto:lashon.gaines@WellsFargo.com>

• ###### 11. Re: How to aggregate an aggregation

either way will work

• ###### 12. Re: How to aggregate an aggregation

If you view the page with both charts, I only need the difference in the raw totals. For example, January is showing 503 scheduled due the other sheet shows 463 completed total. The difference should be 40 but the final calculation is not giving me that total. I have a dimension named Completed, can you drag that into your workbook and see if it adjusts the totals?

Regards,

LaShon A. Gaines

Operational Metrics Analyst

Information Security Risk Assessment and Consulting (ISRAC – COE)

Enterprise Information Security (EIS)

Office: 980.701.5630  |  Email: lashon.gaines@WellsFargo.com<mailto:lashon.gaines@WellsFargo.com>

• ###### 13. Re: How to aggregate an aggregation

I'm sorry, I was not able to use your solution, I am only looking for basic subtraction of my totals. I will keep researching to see if there is anything else that I can find that will help me on the internet. The above information is confusing due to the initial calculations working (didn't include nulls) but not giving me the expected results. When I used ZN in the calculation it didn't work either. Thanks for taking a stab at it, I will ask my manager if we can use maybe another database solution to obtain the actual totals for this case. I really appreciate your help!