7 Replies Latest reply on May 2, 2013 4:07 AM by Marc Freixa

Count different aggregated results

Hi All!

I have a question and I'm not finding how to get along with it!

I want to create a field (or measure) that is 1 (active) or 0 (not active) depending on certain conditions. It works well. But now I want to count how many Actives and not-actives exists per different cells.

Here I attach an example:

I want to know how many weeks per months are active and how many are not-active (and then work with them as a subgorup).

A week is active if the sum of all the activities during that monh is greater than 0 and is not-active otherwise.

As I said, it is very easy to calculate this, but the result I have is:

(see below the table)

 Month Week Channel Activity 1 Activity 2 Activity 3 Activity 4 Activity 5 jan 1 A 0 4 5 2 7 jan 1 B 5 10 6 5 10 jan 1 C 5 6 8 6 4 jan 1 D 8 2 1 6 0 jan 1 E 3 9 5 7 7 jan 2 A 4 4 5 5 10 jan 2 B 3 0 5 6 3 jan 2 C 7 8 8 2 3 jan 2 D 10 3 3 1 5 jan 2 E 5 0 7 9 2 jan 3 A 0 0 0 0 0 jan 3 B 0 0 0 0 0 jan 3 C 0 0 0 0 0 jan 3 D 0 0 0 0 0 jan 3 E 0 0 0 0 0 jan 4 A 3 6 9 7 9 jan 4 B 10 9 6 2 6 jan 4 C 6 10 5 0 5 jan 4 D 2 7 8 3 4 jan 4 E 10 4 3 8 6 feb 1 A 2 8 7 0 6 feb 1 B 2 9 10 3 7 feb 1 C 1 10 9 5 9 feb 1 D 8 4 6 2 5 feb 1 E 2 0 3 10 6

That's what I find (which is correct):

 Month Week jan 1 1 jan 2 1 jan 3 0 jan 4 1 feb 1 1 feb 2 0 feb 3 0 feb 4 1

But I want a table like this:

Jan: 3 (three active weeks)

Feb: 2 (two active weeks)

I don't know how to do it (without changing the database structure (or excel)....

I also attach the whole tableau (v7) with all these....

Thanks a lot!

marc.

• 1. Re: Count different aggregated results

Is the column of 1's and 0's a calculated field?  If so, you could create a calculated field that references that active calculated field.

For example, create a calculated field, let's call it 'ActiveWeeks', with the following expression:

IF [YourCalcField] = 1 THEN 1 ELSE NULL END

Then create a measure to count, using COUNT(ActiveWeeks)

• 2. Re: Count different aggregated results

I can't count because it always says "argument to count is already an aggregation field...."

:-(

Marc.

• 3. Re: Count different aggregated results

Marc, is this what you had in mind?

• 4. Re: Count different aggregated results

Yes.

That's it, but is it possible to do it with Tableau v7?

Marc.

• 5. Re: Count different aggregated results

Sure, same idea.

• 6. Re: Count different aggregated results

I see that the point is that you hve to "extract data"... which I don't really know what it means.... :-(

But thanks! it seems to work!

Marc.

• 7. Re: Count different aggregated results

Thanks a lot! It seems to work!

Do you know if it is possible to have something like this?

Jan: 3 (three active weeks) and 1 inactive week

Feb: 2 (two active weeks) and 2 inactive weeks.

Without creating another "InActive" variable... Something like assigning 0 to non-active and 1 to active and then counting distinct values (0 and 1)....

Thanks!

M.