This is really interesting and I can't get my head around it - we are trying to use the distinct count of subcategory IDs as a partition for the window calc. I can get your answer, but only by stacking marks in a bar chart - see pic. Can't think of how to do this otherwise.

Ha, okay, the table calc is simply window_sum(countd([Cat IDs])), but the secret sauce is that we have to be able to use the countd of subcategories in the table calc - we do this by right clicking on the pill and deselecting - see pic. We then compute using this.

We can now partition based upon this - see tbwx

Wrong book, sorry:

Here's a patch for the more general case of "Cannot aggregate an aggregate..."

I -- and no doubt, others -- have a quick and dirty hack for when we need to Aggregate a COUNTD Calculated Field (or other calculation that is considered Aggregate):

Just either add zero, or multiply by 1.  The resulting Calculated Field is not considered an Aggregate, and thence, it is possible to Aggregate it in your viz.  It doesn't hurt to Optimize the Extract if dealing with a lot of records with this kind of calculation in it.

Nice Ken, thanks for this - I was unaware of this

Ken I agree with Alex, very nice tip. But I'm also thinking your Bio is missing a bit of specificity... Tableau, right?

--Shawn

Hi Shawn,

No I'm just a customer.

Then I'm truly impressed! Glad you jumped in. (And I like the new avatar that just popped up!) Civil War, that's unusual.

--Shawn

Message was edited by: Shawn Wallwork

You may want to try this instead as the table calculation:

IF FIRST()==0 THEN 

  window_sum(countd([Category ID]),0,IIF(FIRST()==0,LAST(),0)) 

END

This limits the number of times the calculation is done - I would be interested if it speeds it up