7 Replies Latest reply on Oct 28, 2016 9:05 AM by noah Carraher

# Creating rank order for small mutiple graph with aggregate values

I am new to making small multiples and using LODs, so bear with me. My small multiple graphs are based on a aggregate value. Whenever I try to rank my small multiple graphs I get the error that I am mixing aggregate and non aggregate. Can someone show me the basic way to solve this type of problem?I created a simplified version of what I am attempting to do in the attached workbook.

Thanks in advance, Noah

• ###### 1. Re: Creating rank order for small mutiple graph with aggregate values

Ni Noah,

Not sure if I understand your challenge but find below my approach and stored in attached workbook version 9.3

I am able to bring all defined measures and I am sorting them based on

The order is 1. left upper, 2. right upper, 3. left lower 4. right lower

• ###### 2. Re: Creating rank order for small mutiple graph with aggregate values

Thanks Norbert, for some reason this wasn't working for me. I will take a look at your work and see what I may have been doing wrong.

Thanks again, Noah

• ###### 3. Re: Creating rank order for small mutiple graph with aggregate values

One more question --  I want to rank my small multiple graphs by the most recent value (called PercentControl2) . But when I use the following syntax I get the error that I am mixing aggregate and non aggregate. I understand the error but I am unclear about the solution.

IF {FIXED [ClinicName]: max([CreateDate])}= [CreateDate] then [percentcontrol2] END

Thanks in advance, Noah

• ###### 4. Re: Creating rank order for small mutiple graph with aggregate values

Hi Noah

Try this one

IF attr([Create Date])=attr({FIXED [C]: max([Create Date])}) then [percentcontrol2] END

Hope it helps..

• ###### 5. Re: Creating rank order for small mutiple graph with aggregate values

Thanks, Norbert, that does work. Tableau accepts it without an error. However, when I go to sort my Clinic field by this expression it does not appear in the list of choices. In the drop down list I can see everything else but not this particular field.. Odd. Any guess as to why it would not appear.

If it helps cast any light, my end goal here is to mimic what Andy Kriebel did in this demo

Tableau Tip Tuesday: How to Create Small Multiple Line Charts

In his workbook , he builds something like this

IF {FIXED [Candidate] : MAX([Poll Date End])} = [Poll Date End]

THEN [Net Favorability]

END

He then uses this in his sort to sort all Candidates by their most current rating. Makes sense. I just want to to do the same thing with clinics and percent control. My calculation is very similar, Percentcontrol2 being analogous to Andy's Net Favorability. Except for the fact that my PercentControl2 is an aggregated field ... What I am not understanding is what I need to do to my PercentControl field to make Tableau happy and allow me to Select --> Clinic name --->sort --> sort by this new field based on the above calculation (most recent percent control for each clinic)

Thanks in advance,

Noah

• ###### 6. Re: Creating rank order for small mutiple graph with aggregate values

Hi Noah,

Hmmm....Did the analyses myself. For sure it has to do something with the calculation. But can't figure out what is accepted and what not.

We will ask one of the top-guys on the block. @Mark Fraser  could you elaborate on this one. Your insides are appreciated. Upfront. Thanks a TON!

• ###### 7. Re: Creating rank order for small mutiple graph with aggregate values

Thank you for following up , Norbert.

Just to lay out the problem for Mark or others who may want to weigh in, I have a expression for a percent calculation (percentcontrol2)

count(if [PCDM_Num]=1 then 1 end )/ count (if [PCDM_Den]=1 then 1 end)

And I have a set of small multiples that breaks down by individual clinics and shows this percent calculation by month at each clinic

Where I am stuck is that I also need to order these clinics based on the most recent percent.. Very similar to how Andy Kriebel  rated his candidates by their net favorability . But when I copy Andy's calculation for doing this I get an Aggregate/NonAggregate error , which I cannot fix. The reason for this appears to be that whereas his net favorability field is a straight unaggregated integer, mine is an aggregated percent.

When I do the following  IF attr([Create Date])=attr({FIXED [C]: max([Create Date])}) then [percentcontrol2] END  the error goes away  , however, this expression which I call Sort still has a glitch which is that it does not show up in the list of fields that I can choose to sort the graphs.

Attached is  a workbook with a dummy data version of the problem that I am seeing. In the workbook you can see the Sort under measures, but when you try to sort the clinicCode  by Sort,  Sort does not appear in the list to choose from ...

thanks in advance , noah