4 Replies Latest reply on Oct 15, 2013 6:16 AM by John Swimming

# Calculated field with constant custom level of aggregation (even without LoD)

Hi friends!

I am new to the forum, and I am loving Tableau.

I've been 2 days struggling with this simple thing as I am just starting with Tableau... I'm sure it has to be straightforward!!

Sorry for the following example, but I have no iexplorer at home, and so I cannot do a sample tableau. Att work time I am now completly busy and have no time so I take profit on my free time:

This case is what I want to figure out:

Country   State        City    AVG(Revenue) AVGof[AVG(Revenue) at City Level]

USA    New hampshire  Dover       10               10

USA    New Hampshire  Manchester  15               15

USA    New Hampshire  Concord     20               20

USA    Florida        Orlando     20               20

USA    Florida        Tampa       30               30

UK     England        Dover       30               30

UK     England        Manchester  40               40

Country   State                AVG(Revenue) AVGof[AVG(Revenue) at City Level]

USA    New hampshire              15               15

USA    Florida                    25               25

UK     England                    35               35

Country                        AVG(Revenue) AVGof[AVG(Revenue) at City Level]

USA                               19*              20**

UK                                35               35

*Avg of 10,15,20,20,30 = 95/5 = 19

**Avg of 15 and 25

I am obliged to show only Country + both AVG measures. So I need to play with Level of Detail.

I would like to understand how to solve both approaches, with Level of Detail = Country, and with Level of Detail = City

I learnt well WINDOW functions and this is what happens

* If I am at Country level, I cannot specify City as Level for the WINDOW_SUM function, it is not selectable in the drop-down box

* If I am at City Level, the values are like usually not added when aggregated but shown in row!!!   So I get something like AVG = 15 25

So at the end is like asking a constant value, that is not affected whether you are a level above or below, since it calculates internally

.

How would you solve both cases!!

• ###### 1. Re: Constant level of aggregation in a calculated field even without LoD

Hi and welcome to the forums!

It sounds like you will probably need to do Advanced compute using. Can you post the sample workbook when you get into work?

-Tracy

• ###### 2. Re: Constant level of aggregation in a calculated field even without LoD

Thank you for your interest and offering me help!

I can't, that's the problemm I woudl not have lost time wrinting manually a sample if I could. I have very few free time at work, so I have to do these things at home, and there I use Tableau Public and I don't have iexplorer so it does not work saving online.

It is more a theoretical question, that is why I used txt-like tables.

It seems like I cannot use Compute Using, because I need the LoD of City for addressing, and then the result is unwantes, as I need a LoD of Country.

I'd like to know how to do constant (in the sense of ATTR())  custom-level aggregations (in my sample would be the second field AVGofAVGs)  in Tableau without relying on WINDOW_funcs + LoD. I'm sure I am forgetting something basic.

I just want to get last table shown working. Its a root hierarchy dimension + 2 fields. One of those is an ordinary AVG(Revenue), the second one is the one I am actually looking for, I think the example is straightforward. And after all, I am asking for fishing rods instead of fishes so don't get me wrong

• ###### 3. Re: Calculated field with constant custom level of aggregation (even without LoD)

I don't know if it helps, but have you tried placing State/City on the Level of Detail so they can be referenced in a calculation, but not necessarily show up in the view?  This type of question is difficult to answer without an example--you could mock one up fairly quickly in the Sample data provided by Tableau, and post it here so you can get the best possible help available on the Forum.

• ###### 4. Re: Calculated field with constant custom level of aggregation (even without LoD)

I finally realized I need them in LoD as you said.

And I learn also to filter using IIF(INDEX=0) for hiding repeated calculations