8 Replies Latest reply on Nov 18, 2016 2:00 AM by Eshwar Prasad

# Aggregation and Non Aggregation Error in the calculation

HI team,

i have calculation like below. But i am getting Aggregation and Non Aggregation Error.

if COUNTD([Quarter])=4 then [Region]

ELSE [Ship Mode]

END

Can any one help me  how to handle it. But i have the calculaiton exactly like above

• ###### 1. Re: Aggregation and Non Aggregation Error in the calculation

Hi,

Could you clarify what this calculation should do?

In this case it does a countd (on a dimension in your view), and depending on the result it gives either the region or the shipmode.

In your example there is no dimension to for the countd, so: no results. If you add a dimension (like region) into the view, the calculation works.

• ###### 2. Re: Aggregation and Non Aggregation Error in the calculation

Try this:

if COUNTD([Quarter])=4 then ATTR( [Region])

ELSE ATTR( [Ship Mode])

END

I just opened your workbook.  I see you tried that.

What are you trying to do in here?

(And what version are you working in?  When I open this in 10.0 it upgrades.  If I make a change and re-post the workbook, you won't be able to open it.)

• ###### 3. Re: Aggregation and Non Aggregation Error in the calculation

Hi Matthijs,

i will explain my requirement.

i have year and quarter filters.

and i have different sales for Quarter and Year.

if select single quarter then sales will be different. and if u select all quarters then total year sales should come.

But summation of the individual sales wont be equal to the total year sales. thats how my data and requirement is.

so i have defined two sales, quarterly sales and Monthly sales.(which is working as expected)

PFA for calculation details.

and if i select single quarter i should display regions, if i select all quarters i should display ship mode as dimension.

so how did to get the quarterly sales and yearly sales, i used the sames logic for dimensions also.

like

if  Count(Quarter)=4 then region

else ship Mode

end.

This calculation is giving error.

• ###### 4. Re: Aggregation and Non Aggregation Error in the calculation

Sorry forgot to attach file

• ###### 5. Re: Aggregation and Non Aggregation Error in the calculation

I've never done this without parameters. I create parameters where the enduser selects the kind of view (year total or quarterly). A calculated field based on the parameter then provides the wanted view.

I hope this helps.

• ###### 6. Re: Aggregation and Non Aggregation Error in the calculation

Hi Joe,

i am using 9.3.

i tried with that calculation with attr function. But riogions and shipmode values are not diaplaying.

• ###### 7. Re: Aggregation and Non Aggregation Error in the calculation

See attached.

To use the dimensions as you are doing in your existing calcs, you have to have the dimension(s) you are referencing on your sheet.  So see Sheet 2.  It's just a copy of Sheet 1, but I added [Region] to the detail shelf.  Now Tableau "sees" the various regions, and will separate your marks by region.  On Sheet 3 I did the same with [Ship Mode], and deselected one quarter.  Now you can see ship modes.  But to be able to have your sheet do both, you would have to put both dimensions on the detail shelf, and then Tableau would segment by both dimensions, and it gets messy.  (You can try that and see.)

What's happening here is that your swapping calc has to be treated like a measure because there is an aggregation in the arithmetic that calculates it.  (COUNTD is an aggregate function.)  You'll never get it to be a dimension to behave the way you want it.

This is where the LOD feature that Tableau introduced in 9.0 can be very powerful for you.

Some LODs end up being measures (INCLUDE and EXCLUDE LODs), but the FIXED LOD can be a dimension.  It's important to note, though, the FIXED LODs execute BEFORE your dimension filters, so under normal circumstances it won't see what your user selects from [Quarter].  However there is also a nifty feature with filters that lets you implement a filter before a FIXED LOD executes, and that is by making it a Context Filter.

See SHeet 4.  I did right-click on the Quarter filter and change it to "add to Context".  (The filter pill turns from blue to grey to help you distinguish this visually.)

Next I created a calc called [How Many Quarters?].  It's a FIXED LOD.  Notice that it resides in the dimensions list.  I added this to the title of the sheet so you can see it working as you change quarter selections.

Then I created a new calc similar to your dimension calc.  See [Swap Dimensions].  It also resides in the dimensions list.  I replaced your calc with this one on the ROWS shelf.  Notice that it no longer has ATTR() around it like your calc did when it was on the sheet.  (The fact that Tableau puts ATTR there is an indication to you that Tableau is not treating the field like a dimension.)

Now when you change quarters, you get the swapping of dimensions you're looking for.

• ###### 8. Re: Aggregation and Non Aggregation Error in the calculation

Thank you so much JOE for detailed explanation.

That is very helpful..............

Thank you, thank you so much again