8 Replies Latest reply on Apr 11, 2017 7:11 AM by michael shin

# Aggregate of aggregate  (e.g., Sum of counts)

Hello all. I have a metric that is tracked by DATE from the following calculated field:

if [Rate%] >= .9 then 1

else 0

END

I then wish to aggregate the view to WEEK.  As above calculation is by DATE, I'd like to sum up the daily boolean field.  So, we may have the following:

Week 1:  3

Week 2:  0

Week 3.  5

etc.

Tableau is NOT letting me do a sum of the above.  Rate%  is a ratio of CountD(A) / CountD (B).  So, I am aggregating an aggregate.  Any good work-around?

Mike

• ###### 1. Re: Aggregate of aggregate  (e.g., Sum of counts)

I'm not 100% sure I understand your question and without any sample data I can't exactly say for sure but I think this is what you are looking for:

• ###### 2. Re: Aggregate of aggregate  (e.g., Sum of counts)

Hello there!  Thank you for the quick reply!  Just so you know Rate is a ratio like below:

COUNTD(A) / COUNTD(B)

For the calculated field we will need a sum right?  I want to get a sum of the daily values at week level.

MIke

• ###### 3. Re: Aggregate of aggregate  (e.g., Sum of counts)

Hi Michael,

did you try using LoD Function INCLUDE?

for calculated field Rate try using {INCLUDE 'Day Dimension': COUNTD(A)/COUNTD(B)}  and this calculation can be used in aggregation.

hope that helps.

• ###### 4. Re: Aggregate of aggregate  (e.g., Sum of counts)

Thank you all for the replies.  Attached is mock-up data.  Again, below are my metrics:

Rate = CountD (A) / CountD (B)

# Occurrences =

if [Rate]>= .9 then 1

else 0

END

Per the Mock up data, how do I get a SUM of # occurrences by Week? I will NOT show Date and only display WEEK. Ex: For week of 3/26, # Occurrence would aggregate to 4 for that week. I'm having trouble with LOD syntax to allow me to aggregate # Occurrence further.

Mike

• ###### 5. Re: Aggregate of aggregate  (e.g., Sum of counts)

Yeah, when you put it on the view it will do a sum (you can see it on the Measure Values card as SUM(test) and that is how i got my result.

p.s. I don't see your mock up data...

• ###### 6. Re: Aggregate of aggregate  (e.g., Sum of counts)

Hi Michael,

Is that what you're looking for? I've used your given calculation for occurrences.

Right click on your date field and drop it on rows shelf and select discrete week datepart and occurrences like that.

Let us know if you've any query.

Mahfooj

• ###### 7. Re: Aggregate of aggregate  (e.g., Sum of counts)

Hello there.  Thank you for the reply!  The issue is that you cannot further aggregate an aggregation.  For example, for the week of 3/26, the expected aggregated # occurrences would be 4.

• ###### 8. Re: Aggregate of aggregate  (e.g., Sum of counts)

Hi Muralidhar.  Your solution is actually the right one. I did not apply it correctly. By using FIXED LOD on the Day of Date field, I was able to aggregate this further by week.  Along with Context filter, I got my desired result.  Thank you!