3 Replies Latest reply on May 16, 2018 11:40 AM by Bryce Larsen

# Different aggregations for Day,Month and Quarter

Hi All,

I have to display the total population for day, month, quarter, year.  total population is fixed to month.

If I select parameter Day, total population is 400,000 for May and 420,000 for April. If I select Month, it should be same.

How ever, when we select Quarter, I should get Avg of 400,000 and 420,000  and not sum. Same for the year. I cannot get this.

so for quarter, it should be Avg(sum(total population for month1)+sum(total population for month2)+sum(total population for month3))

and for the year Avg(sum (total population for 12 months or till current month)).

How can we do this in tableau?

I have attached the workbook.

• ###### 1. Re: Different aggregations for Day,Month and Quarter

Hello!

I've attached the following here. This was done under the assumption that the same population value would appear for each day in the month as that's how I read your comment.

I then used a combination of Window calculations to perform the task at hand. Let me know if this works!

Best,

Bryce

• ###### 2. Re: Different aggregations for Day,Month and Quarter

Thank you very much. this worked for me. Can you please tell me how did this calculation resulted the correct value? Thanks, Susheela

• ###### 3. Re: Different aggregations for Day,Month and Quarter

I can try! Here's the same workbook with the final calc having parts _1 and _2.

WINDOW_SUM(SUM([CF_Population_ByMonth]))

This will return 420,000 for all April dates. Regardless if showing all 3 days or just the monthly view (thanks to the LOD calculation already done). This LOD was important as to not inflate the population if, for instance, May had 4 dates in the dataset vs. April's 3. Now we'll always have one row for a month behind the scenes. When you're looking at it by year, there's still the summing of the different months occurring.

WINDOW_SUM(COUNTD([Month Year]))

Thankfully you already had this in there! You wanted to count the number of months in the range or year, so it's a count distinct (April = 1, May = 1), but then summed for the entire view (2, in this case).

Hopefully this makes sense! Hardest part is then determine what to "Compute Using" ("Cell" in this case).