7 Replies Latest reply on Feb 21, 2012 12:13 AM by Gareth Jones

CNT on a calculated text column?

Hi All

I am trying to do the following, and I hope it can be done!

My data:

Customer NumberMonth 1Month 2Month 3Is Active?
123456789000Inactive
126457897000Inactive
326598741645Active
666666666310Active

Basically, in a single table, I have Customer Number, Month 1, 2, 3.  I am then using a calculation of IF (SUM [Month 1], [Month 2], [Month 3]) >0 THEN "Active" ELSE "Inactive" END

And I am then using the new calculated field as if it was a column.  And it works, I can make the data look like this:

Customer NumberIs Active
123456789Inactive
126457897Inactive
326598741Active
666666666Active

However I am really needing to view the data like this:

Active2
Inactive2

So either I'm really not that great at putting these into the right shelf, or am I going about this wrong?

I need this sort of calculation on quite a few other columns to produce these "calculated text fields" so any help would be GREATLY appreciated!

Thanks

Gareth

• 1. Re: CNT on a calculated text column?

I've attached an example showing one way to do it (I'm sure there are other ways).

I used table calculations, and there are quite a few concepts to grasp there, so I'm not going to try a full explanation - there are lots of resources you can read up on table calculations. Basically the WINDOW_COUNT() allows you to count the results of your initial calculation. One trick here is that I have unchecked the "Ignore in table calculations" checkbox on the pill for the [Is Active] calculated field - you need to do that in order to be able to partition the customers by the [Is Active] status for the table calculation.

• 2. Re: CNT on a calculated text column?

Thank you SO MUCH for the effort - I will take a look, I'm really good at figuring things out if I can see examples, so I really appreciate your help.   Hopefully I'll be back shortly with news of success!

• 3. Re: CNT on a calculated text column?

Richard it appears there is a linked spreadsheet missing from your package?

• 4. Re: CNT on a calculated text column?

Well I think that's a Tableau bug - I saved it as a packaged workbook and it didn't include the spreadsheet.  I've never seen that happen before.

I've re-saved it and it's packaged it OK this time.

1 of 1 people found this helpful
• 5. Re: CNT on a calculated text column?

Richard

Thank you for that - I have managed to construct that now in my data set, however I don't think it's too happy with my 280,000 customer records - currently sitting at 38 minutes calculating!

Do you have any other suggestions to how to handle this?  I may have to calculate this in Excel and import it as a static column, and perform a count on that.

Thanks

• 6. Re: CNT on a calculated text column?

You can do the calculation in the data source (in this case the JET engine) rather than in table calculations, by defining the [Is Active] field in a custom SQL connection.

Take a look at the new sheet I've added and the custom SQL connection it uses.  I think that will be much quicker for the number of rows you are talking about.

• 7. Re: CNT on a calculated text column?

Richard, you are a GENIUS!  That worked!  I didnt realise that my multiple tables being selected was actually just a giant SQL query.  I modified your suggestion and created exactly what I need in my current table selections.

You are a lifesaver!!!